+ Reply to Thread
Results 1 to 4 of 4

Excel 2003 Difficulty with private sub code

Hybrid View

  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,788

    Re: Excel 2003 Difficulty with private sub code

    Here is the Error part

    Private Sub Worksheet_SelectionChange1(ByVal Target As Range)


    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