Problem with Worksheet_Change event (2002/SP3)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks for all your help.

Reply | Quote