Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default Ambiguous name detected: worksheet_change

I guess I have two different commands that are interfereing with each other. I have no clue if they can be combined or how to do that. Any 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:D")) 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")) Is 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 Sub
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Ambiguous name detected: worksheet_change

On Tuesday, July 10, 2012 9:38:45 PM UTC+5:30, GS wrote:
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ambiguous name detected - help how fix? Chet Excel Programming 1 July 15th 09 09:09 PM
Excel 2007 ambiguous name detected:Worksheet_Change Steved Excel Programming 3 February 7th 08 10:03 PM
Ambiguous Name detected? davegb[_2_] Excel Programming 2 December 7th 07 05:09 PM
ambiguous name detected mark Excel Programming 4 February 4th 04 03:01 PM
Ambiguous Name Detected:~ jaf Excel Programming 0 September 12th 03 12:56 AM


All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"