Two Change Events on Same Sheet

mtaylor50

New Member
Joined
Oct 4, 2004
Messages
28
In attempting to place two change events in the same worksheet, I get an error message saying "Ambiguous Name". As a VBA newbie, I've been unable to correctly interpret the Help section (despite many trials and errors). Here's what's supposed to happen:
1) When a customer orders a product, staff enters the $ amount in column K. This activates a date-stamp in column A.
2) When the customer's payment is received, staff enters the $ amount in column L, which activates a date-stamp in column X.

Each of the codes below works fine when it is the only change event. But when I add the second event, the "Ambiguous Name" message appears. I've attempted numerous edits without success. Any help is much appreciated. Thank you.
Mark

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("K:K")
If Intersect(Target, rng) Is Nothing Then Exit Sub
ActiveSheet.Unprotect ("e")
Target.Offset(0, -10) = Date
ActiveSheet.Protect ("e")
End Sub
------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("L:L")
If Intersect(Target, rng) Is Nothing Then Exit Sub
ActiveSheet.Unprotect ("e")
Target.Offset(0, 12) = Date
ActiveSheet.Protect ("e")
End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
You can only have one change event in existance, you had two, so vb didn't know which one to look at. I have adjusted your code so that it will work all together.

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim rng As Range 
' Only look at single cell changes 
If Target.Count > 1 Then Exit Sub 
Set rng = Range("K:K") 
If Intersect(Target, rng) Is Nothing Then Exit Sub 
ActiveSheet.Unprotect ("e") 
Target.Offset(0, -10) = Date 
ActiveSheet.Protect ("e") 

Set rng = Range("L:L") 
If Intersect(Target, rng) Is Nothing Then Exit Sub 
ActiveSheet.Unprotect ("e") 
Target.Offset(0, 12) = Date 
ActiveSheet.Protect ("e") 
End Sub

HTH
Cal
 

mtaylor50

New Member
Joined
Oct 4, 2004
Messages
28
Thanks, Cal.
Yes, I had tried that earlier. I also pasted your amended code into the VBE with the same result, which was partially successful: I did not get the "Ambiguous Name" message. However, while the date-stamp activated in column A after entering $ in column K, the date-stamp did not activate in column X after entering $ in column L. Any other suggestion? Thanks.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
    ' Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    
    Set rng1 = Range("K:K")
    Set rng2 = Range("L:L")
    
    If Intersect(Target, Union(rng1, rng2)) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    Me.Unprotect ("e")
    
    Select Case Target.Column
        Case 11
            Target.Offset(0, -10) = Date
        Case 12
            Target.Offset(0, 12) = Date
    End Select
    
    Application.EnableEvents = True
    Me.Protect ("e")

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,335
Messages
5,571,618
Members
412,409
Latest member
kellotheym
Top