+ Reply to Thread
Results 1 to 4 of 4

Excel 2003 Difficulty with private sub code

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Plymouth, SW England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Excel 2003 Difficulty with private sub code

    I have a spreadsheet in which I have a number of columns where information is entered. What Iím trying to achieve is that when a date is entered in one column another column becomes mandatory and no further information can be entered until this column is filled. The columns in question are B and C. e.g. If a date is entered in cell B3 a response must be entered in C3. I have tried to adapt the following code to achieve this but without much success


    Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    Dim myCell As Range
    Dim myRange As Range

    For Each myCell In Range("B3:B102")
    If (myCell.Value) = ("dd/mm/yy") Then
    If Len(myCell.Offset(, 1).Value) = 0 Then
    MsgBox "You must enter the nature of the complaint", vbCritical, "Additional Information Required"
    Application.EnableEvents = False
    myCell.Offset(0, 1).Select
    Application.EnableEvents = True
    Exit Sub
    End If
    End If
    Next myCell
    End Sub

    many thanks for your assistance

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Excel 2003 Difficulty with private sub code

    Here is the Error part

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    Plymouth, SW England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Excel 2003 Difficulty with private sub code

    I have an existing Private Sub Worksheet_SelectionChange(ByVal Target As Range) which is why i added the 1 to this commend

  4. #4
    Registered User
    Join Date
    04-22-2013
    Location
    Plymouth, SW England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Excel 2003 Difficulty with private sub code

    Many thanks for the guidance, I have amalgamated it into the original Private sub with the following code. Much appreciated

    Next myCell
    For Each myCell In Range("B3:B102")
    If myCell.Value >= 1 Then
    If Len(myCell.Offset(, 1).Value) = 0 Then
    MsgBox "You must enter the nature of the complaint before continuing", vbCritical, "Nature of complaint Required"
    Application.EnableEvents = False
    myCell.Offset(0, 1).Select
    Application.EnableEvents = True
    Exit Sub
    End If
    End If
    Next myCell
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1