Hello,
I have some code in a sheet, and it executes well. I added a second sub, but when I run it, I get this message:
Compile Error: Ambiguous name detected: Worksheet_Change
Here are the two subs:
Private Sub Worksheet_Change(ByVal Target As Range)
' Only execute this code if changed cell is F9
If Target.Address <> "$F$9" Then
Exit Sub
End If
Dim NewValue As Variant
' Turn off events and screen updating
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
NewValue = Target.Value
Application.Undo
' If the old value is different from the new value
If Target.Value <> NewValue Then
' Assign the Newvalue to the changed cell
Target.Value = NewValue
'Clear the required range
Range("F10").ClearContents
End If
' Re-enable events and screen updating
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Only execute this code if changed cell is F13
If Target.Address <> "$F$13" Then
Exit Sub
End If
Dim NewValue As Variant
' Turn off events and screen updating
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
NewValue = Target.Value
Application.Undo
' If the old value is different from the new value
If Target.Value <> NewValue Then
' Assign the Newvalue to the changed cell
Target.Value = NewValue
'Reset current year months to FALSE (i.e., no actuals)
Range("TrueMonth1") = False
Range("TrueMonth2") = False
Range("TrueMonth3") = False
Range("TrueMonth4") = False
Range("TrueMonth5") = False
Range("TrueMonth6") = False
Range("TrueMonth7") = False
Range("TrueMonth8") = False
Range("TrueMonth9") = False
Range("TrueMonth10") = False
Range("TrueMonth11") = False
Range("TrueMonth12") = False
End If
' Re-enable events and screen updating
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
As you can see, the second sub is similar in structure to the first sub, which is why I used it. What should I do to fix this?
Paul
Hi Paul
You cannot have more than one Worksheet_Change event in a Worksheet.
Give this code a try. It has 2 events required to track the change in the values.
Option Explicit
Dim OriValue
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Cells to monitor for changes
If Not Intersect(Target, Range("F9, F13")) Is Nothing Then
'Store the original cell's value for comparison
OriValue = Target
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F9")) Is Nothing Then
'Value have changed
If OriValue <> Target.Value Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Clear the required range
Range("F10").ClearContents
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End If
If Not Intersect(Target, Range("F13")) Is Nothing Then
'Value have changed
If OriValue <> Target.Value Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Reset current year months to FALSE (i.e., no actuals)
Range("TrueMonth1") = False
Range("TrueMonth2") = False
Range("TrueMonth3") = False
Range("TrueMonth4") = False
Range("TrueMonth5") = False
Range("TrueMonth6") = False
Range("TrueMonth7") = False
Range("TrueMonth8") = False
Range("TrueMonth9") = False
Range("TrueMonth10") = False
Range("TrueMonth11") = False
Range("TrueMonth12") = False
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "Changed F13"
End If
End If
End Sub
Hope this helps
Sunny
Sunny,
Success! It works great.
Thank you, Paul
