Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE**
Hi all -
I am getting a Compile error: Ambigious name detected: Worksheet_Change message when processing my code. From reading various posts, I see that I can only use the Worksheet_Change process once. In my code below, how can I combine these procedures so I can make this work? Thanks for your help! '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "d5" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Range("c6:c24").Copy Range("c7:c25") Range("c6").Value = Target.Value End If ws_exit: Application.EnableEvents = True End Sub '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "q5" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Range("p6:p24").Copy Range("p7:p25") Range("p6").Value = Target.Value End If ws_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE**
Hi
This should work: Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- On Error GoTo ws_exit: Application.EnableEvents = False If Not Target.Address = "D5" Then Range("c6:c24").Copy Range("c7:c25") Range("c6").Value = Target.Value ElseIf Target.Address = "Q5" Then Range("p6:p24").Copy Range("p7:p25") Range("p6").Value = Target.Value End If Application.EnableEvents = True ws_exit: Application.EnableEvents = True End Sub I don't see the need for the EnableEvents or error code? regards Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE**
Hi Paul,
If Not Target.Address = "D5" Then I suspect that an exraneous 'Not' has crept into your code. Additionally, I suspect that you intended "$D$5" rather than "D5". Similarly, I assume that ElseIf Target.Address = "Q5" Then was intended as: ElseIf Target.Address = "$Q$5" Then I don't see the need for the EnableEvents or error code? If events are not disabled, the copy operation(s) will re-trigger the Worksheet_Change event and it is good practice to restore Application settings in an error handler. Having, however, restored the Events setting in the error handler, it is not necessarry to additionally restore Events immediately prior to the error handler. --- Regards, Norman wrote in message ups.com... Hi This should work: Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- On Error GoTo ws_exit: Application.EnableEvents = False If Not Target.Address = "D5" Then Range("c6:c24").Copy Range("c7:c25") Range("c6").Value = Target.Value ElseIf Target.Address = "Q5" Then Range("p6:p24").Copy Range("p7:p25") Range("p6").Value = Target.Value End If Application.EnableEvents = True ws_exit: Application.EnableEvents = True End Sub I don't see the need for the EnableEvents or error code? regards Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE**
This was great! Thanks Paul and Norman for your help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error message - Errors were detected while saving FILENAME | Excel Discussion (Misc queries) | |||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE** | Excel Discussion (Misc queries) | |||
VBAProject name compile error, not defined at compile time | Excel Programming | |||
Errors Detected Error Message | Excel Programming |