Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with Worksheet_Change event (2002/SP3)

    I have a code in which in one of its actions it assigns a value to C3. Once this code is complete, I have setup an event which would run a macro if C3 has changed in value.

    Upon running this code, I found that once C3 has been assigned a value it would go through Worksheet_Change event and would run another macro there. This is not what I have intended.

    Is there a way to assign a value C3 the first time without calling on Worksheet_Change event, while any subsequent changes in C3 would use the event?


    Sub test
    Cells(3, 3) = 10
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim ValidateCode As Variant
    On Error GoTo ErrHandler
    Application.EnableEvents = False

    If Target = Cells(3, 3) Then
    ValidateCode = EntryIsValid(Target.Value) ' A check that is an integer
    If ValidateCode = True Then
    MacroCreateSheet ' << Call on a macro to perform an action if C3 is changed
    Else
    MsgBox ValidateCode, vbCritical, "Invalid Entry"
    Target.ClearContents
    Target.Activate
    End If
    End If

    ErrHandler:
    Application.EnableEvents = True
    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Worksheet_Change event (2002/SP3)

    Hi,

    Like you have done inthe worksheet_change event code, use the disable events, change C3, enable events sequesnceif you want to avoid the the worksheet_change event firing again.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Worksheet_Change event (2002/SP3)

    Thanks for the help.

    Another question. Apperantly, the worksheet_change event only works in a specific worksheet, for example Sheet1. I want to create a new worksheet using VBA code and then monitor cell C3 in the newly created worksheet. How do I incorporate the worksheet_change event code in the new worksheet? Do I need to copy the event code to this sheet manually or is there a way to specify that the event monitor will look for changed in C3 in a worksheet of a specific name?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Worksheet_Change event (2002/SP3)

    CFDguru,

    You may be able to use the Woorkbook_NewSheet event to copy the Worksheet_Change code to the new sheet.
    I don't have too much experience in this object type, so ask others to assist if needed.
    Otherwise a manual copy is needed

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem with Worksheet_Change event (2002/SP3)

    Although it's possible to use code to add code to a worksheet, it's tricky, and requires that the user allows programmatic access to the Visual Basic project (it's forbidden by default).

    You could use the Wokkbook_SheetChange event in the ThisWorkbook module instead:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
    Case "Sheet1"
    ' Your code here, taking care to refer to Sh throughout, e.g.
    If Target = Sh.Cells(3, 3) Then
    ...
    End Select
    End Sub

    The event code will be run whenever a cell in any worksheet in the workbook with the code ("ThisWorkbook") is changed. Sh is the worksheet on which the change occurred. so you should use Sh.Range(...) and Sh.Cells(...) consistently.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Worksheet_Change event (2002/SP3)

    Thanks for all your help.

Posting Permissions

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