Ambiguous name detected: worksheet_change

[FlesburgT]

I guess I have two different commands that are interfereing with eac
other. I have no clue if they can be combined or how to do that. An
help would be greatly appreciated. I've listed the code below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A")) Is Nothing Then
With Target
If .Column = 1 Then Cells(Rows.Count
.Column).End(xlUp).Offset(0, 1).Select
If .Column = 2 Then Cells(Rows.Count
.Column).End(xlUp).Offset(0, 1).Select
If .Column = 3 Then Cells(Rows.Count
.Column).End(xlUp).Offset(1, -2).Select
End With
End If

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10000,B1:B10000,C1:C10000")) I
Nothing Then 'set your range here
ActiveSheet.Unprotect Password:=""
Target.Locked = True
ActiveSheet.Protect Password:=""
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
N = Target.Row
If Me.Range("A" & N).Value <> "" Then
Me.Range("G" & N).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Su

 

[GS]

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")) 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

 

[sambadtripura]

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")) 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