View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GS[_2_] GS[_2_] is offline
external usenet poster
 
Posts: 3,514
Default Ambiguous name detected: worksheet_change

Procedure names MUST be unique. In this case you need to append the
code to execute in the 2nd instance of the _Change event to follow it's
counterpart in the 1st instance...

<snip
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:D")) Is Nothing Then Exit Sub

Select Case Target.Column
Case = 1
Cells(Rows.Count, .Column).End(xlUp).Offset(0, 1).Select
If Me.Range("A" & Target.Row).Value < "" Then _
Me.Range("G" & Target.Row).Value = Now

Case = 2
Cells(Rows.Count, .Column).End(xlUp).Offset(0, 1).Select

Case = 3
Cells(Rows.Count, .Column).End(xlUp).Offset(1, -2).Select
End Select

If Target.Cells.Count 1 Or _
Intersect(Target, Range("A1:C10000")) Is Nothing Then Exit Sub

'set your range here
ActiveSheet.Unprotect Password:=""
Target.Locked = True
ActiveSheet.Protect Password:=""
End Sub

...though I'm not clear on what it is you want to accomplish, it's
rarely necessary to select cells to act on them. Your approach seems
rather inefficient to me when there's better ways to accomplish what
this code does without having to select cells or toggle sheet
protection.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion