Ambiguous name detected question

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
Morning all,

I was kindly advised a code for a worksheet i am using but i would also like to use the same code on another worksheet.
My issue is that i get the Ambiguous named detected message because i already have a code using the same name.
This would be Private Sub Worksheet_Change(ByVal Target As Range)

Can you advise how in a situation like this we get around it so it can be used.

On this page in question i have in use.

Private Sub Worksheet_Activate()
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Many thanks.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Why do you need all three of these in the same sheet.

I can see why these two may conflict with each other:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Tell me what your trying to do.
 
Upvote 0
Hi,
The below codes works fine no problem, ive been using it for a while now.

Code: 
Private Sub NewRowButton_Click()With Sheets("HONDA SHEET")
    .Range("A17").EntireRow.Insert Shift:=xlDown
    .Range("A17:G18").Borders.Weight = xlThin
    .Range("G17").Value = Date
    .Range("A13").Interior.ColorIndex = 2
     Range("C1:F12").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("A17").Select
    End With
End Sub


Private Sub CheckButton_Click()
HondaParts.Show
End Sub


Private Sub VinToolButton_Click()
ActiveWorkbook.FollowHyperlink Address:="http://www.vindecoderz.com/EN/Honda", NewWindow:=True
End Sub


Private Sub Worksheet_Activate()
    Range("A13").Select
    ActiveWindow.ScrollRow = 14
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
With ThisWorkbook.Sheets("HONDA SHEET")
If Not Intersect(Target, .Range("A13")) Is Nothing And .Range("A13") <> "" Then
If Len(.Range("A13").Value) <> 17 Then
               .Range("A13").Interior.ColorIndex = 3
               MsgBox ("Honda Chassis Number Must Be 17 Characters, Please Try Again")
                .Range("A13").ClearContents
                .Range("A13").Interior.ColorIndex = 2
                .Range("A13").Activate
Else
                Application.EnableEvents = False
                .Rows(17).Insert Shift:=xlDown
                .Range("A17:G17").Borders.Weight = xlThin
                .Range("G17").Value = Date
                .Range("A17").Value = UCase(.Range("A13").Value)
                .Range("B17").Select
                .Range("A13").ClearContents
                .Range("A17").Characters(Start:=10, Length:=1).Font.ColorIndex = 3
                Application.EnableEvents = True
End If
End If
End With


Target.Interior.ColorIndex = 6
If Not Intersect(Target, Range("F17")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "ACCORD ID 48" Then Range("D1").Value = Range("D1").Value + 1
If Target.Value = "ACCORD ID 8E" Then Range("D2").Value = Range("D2").Value + 1
If Target.Value = "BLACK NRK ID 46" Then Range("D3").Value = Range("D3").Value + 1
If Target.Value = "BLACK NRK ID 48" Then Range("D4").Value = Range("D4").Value + 1
If Target.Value = "BLACK NRK ID 8E" Then Range("D5").Value = Range("D5").Value + 1
If Target.Value = "CIVIC CE0523" Then Range("D6").Value = Range("D6").Value + 1
If Target.Value = "CRV HLIK-1T" Then Range("D7").Value = Range("D7").Value + 1
If Target.Value = "CRV ID 48" Then Range("D8").Value = Range("D8").Value + 1
If Target.Value = "FLIP REMOTE 2B" Then Range("D9").Value = Range("D9").Value + 1
If Target.Value = "FLIP REMOTE 3B" Then Range("D10").Value = Range("D10").Value + 1
If Target.Value = "FRV ID 48" Then Range("D11").Value = Range("D11").Value + 1
If Target.Value = "FRV ID 8E" Then Range("D12").Value = Range("D12").Value + 1
If Target.Value = "G8D-345H-A" Then Range("D13").Value = Range("D13").Value + 1
If Target.Value = "G8D-348H-A" Then Range("F1").Value = Range("F1").Value + 1
If Target.Value = "G8D-350H-A" Then Range("F2").Value = Range("F2").Value + 1
If Target.Value = "G8D-453H-A" Then Range("F3").Value = Range("F3").Value + 1
If Target.Value = "G8D-456H-A" Then Range("F4").Value = Range("F4").Value + 1
If Target.Value = "HON 58 ID 13" Then Range("F5").Value = Range("F5").Value + 1
If Target.Value = "HON 58 ID 48" Then Range("F6").Value = Range("F6").Value + 1
If Target.Value = "JAZZ HLIK-1T" Then Range("F7").Value = Range("F7").Value + 1
If Target.Value = "JAZZ ID 48" Then Range("F8").Value = Range("F8").Value + 1
If Target.Value = "JAZZ ID 8E" Then Range("F9").Value = Range("F9").Value + 1
If Target.Value = "LEGEND ID 8E" Then Range("F10").Value = Range("F10").Value + 1
If Target.Value = "SILVER NRK ID 48" Then Range("F11").Value = Range("F11").Value + 1
If Target.Value = "SILVER NRK ID 8E" Then Range("F12").Value = Range("F12").Value + 1
If Target.Value = "72147-S2H-G01" Then Range("F13").Value = Range("F13").Value + 1
End If
    If Target.Address = "$F$17" Then
        Call sheettolist
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "G"


'   *** Specify start row ***
    myStartRow = 17
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True


End Sub

But now i would like to add this to the page.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)



If Not Intersect(Target, Range("B13")) Is Nothing Then




If UCase(Target.Value) = "A" Then Target.Value = "2010"
If UCase(Target.Value) = "B" Then Target.Value = "2011"
If UCase(Target.Value) = "D" Then Target.Value = "2012"
If UCase(Target.Value) = "E" Then Target.Value = "2013"
If UCase(Target.Value) = "F" Then Target.Value = "2014"
If UCase(Target.Value) = "G" Then Target.Value = "2015"
If UCase(Target.Value) = "H" Then Target.Value = "2016"
If UCase(Target.Value) = "I" Then Target.Value = "2017"
If UCase(Target.Value) = "J" Then Target.Value = "2018"
If UCase(Target.Value) = "K" Then Target.Value = "2019"


End If


End Sub
 
Upvote 0
Try this:

See new part marked in red:
Code: 
Private Sub NewRowButton_Click()
With Sheets("HONDA SHEET")
    .Range("A17").EntireRow.Insert Shift:=xlDown
    .Range("A17:G18").Borders.Weight = xlThin
    .Range("G17").Value = Date
    .Range("A13").Interior.ColorIndex = 2
     Range("C1:F12").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("A17").Select
    End With
End Sub

Private Sub CheckButton_Click()
HondaParts.Show
End Sub

Private Sub VinToolButton_Click()
ActiveWorkbook.FollowHyperlink Address:="http://www.vindecoderz.com/EN/Honda", NewWindow:=True
End Sub

Private Sub Worksheet_Activate()
    Range("A13").Select
    ActiveWindow.ScrollRow = 14
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
With ThisWorkbook.Sheets("HONDA SHEET")
If Not Intersect(Target, .Range("A13")) Is Nothing And .Range("A13") <> "" Then
If Len(.Range("A13").Value) <> 17 Then
               .Range("A13").Interior.ColorIndex = 3
               MsgBox ("Honda Chassis Number Must Be 17 Characters, Please Try Again")
                .Range("A13").ClearContents
                .Range("A13").Interior.ColorIndex = 2
                .Range("A13").Activate
Else
                Application.EnableEvents = False
                .Rows(17).Insert Shift:=xlDown
                .Range("A17:G17").Borders.Weight = xlThin
                .Range("G17").Value = Date
                .Range("A17").Value = UCase(.Range("A13").Value)
                .Range("B17").Select
                .Range("A13").ClearContents
                .Range("A17").Characters(Start:=10, Length:=1).Font.ColorIndex = 3
                Application.EnableEvents = True
End If
End If
End With

Target.Interior.ColorIndex = 6
If Not Intersect(Target, Range("F17")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "ACCORD ID 48" Then Range("D1").Value = Range("D1").Value + 1
If Target.Value = "ACCORD ID 8E" Then Range("D2").Value = Range("D2").Value + 1
If Target.Value = "BLACK NRK ID 46" Then Range("D3").Value = Range("D3").Value + 1
If Target.Value = "BLACK NRK ID 48" Then Range("D4").Value = Range("D4").Value + 1
If Target.Value = "BLACK NRK ID 8E" Then Range("D5").Value = Range("D5").Value + 1
If Target.Value = "CIVIC CE0523" Then Range("D6").Value = Range("D6").Value + 1
If Target.Value = "CRV HLIK-1T" Then Range("D7").Value = Range("D7").Value + 1
If Target.Value = "CRV ID 48" Then Range("D8").Value = Range("D8").Value + 1
If Target.Value = "FLIP REMOTE 2B" Then Range("D9").Value = Range("D9").Value + 1
If Target.Value = "FLIP REMOTE 3B" Then Range("D10").Value = Range("D10").Value + 1
If Target.Value = "FRV ID 48" Then Range("D11").Value = Range("D11").Value + 1
If Target.Value = "FRV ID 8E" Then Range("D12").Value = Range("D12").Value + 1
If Target.Value = "G8D-345H-A" Then Range("D13").Value = Range("D13").Value + 1
If Target.Value = "G8D-348H-A" Then Range("F1").Value = Range("F1").Value + 1
If Target.Value = "G8D-350H-A" Then Range("F2").Value = Range("F2").Value + 1
If Target.Value = "G8D-453H-A" Then Range("F3").Value = Range("F3").Value + 1
If Target.Value = "G8D-456H-A" Then Range("F4").Value = Range("F4").Value + 1
If Target.Value = "HON 58 ID 13" Then Range("F5").Value = Range("F5").Value + 1
If Target.Value = "HON 58 ID 48" Then Range("F6").Value = Range("F6").Value + 1
If Target.Value = "JAZZ HLIK-1T" Then Range("F7").Value = Range("F7").Value + 1
If Target.Value = "JAZZ ID 48" Then Range("F8").Value = Range("F8").Value + 1
If Target.Value = "JAZZ ID 8E" Then Range("F9").Value = Range("F9").Value + 1
If Target.Value = "LEGEND ID 8E" Then Range("F10").Value = Range("F10").Value + 1
If Target.Value = "SILVER NRK ID 48" Then Range("F11").Value = Range("F11").Value + 1
If Target.Value = "SILVER NRK ID 8E" Then Range("F12").Value = Range("F12").Value + 1
If Target.Value = "72147-S2H-G01" Then Range("F13").Value = Range("F13").Value + 1
End If
    If Target.Address = "$F$17" Then
        Call sheettolist
    End If

[COLOR=#ff0000]'New part[/COLOR]
If Not Intersect(Target, Range("B13")) Is Nothing Then
If UCase(Target.Value) = "A" Then Target.Value = "2010"
If UCase(Target.Value) = "B" Then Target.Value = "2011"
If UCase(Target.Value) = "D" Then Target.Value = "2012"
If UCase(Target.Value) = "E" Then Target.Value = "2013"
If UCase(Target.Value) = "F" Then Target.Value = "2014"
If UCase(Target.Value) = "G" Then Target.Value = "2015"
If UCase(Target.Value) = "H" Then Target.Value = "2016"
If UCase(Target.Value) = "I" Then Target.Value = "2017"
If UCase(Target.Value) = "J" Then Target.Value = "2018"
If UCase(Target.Value) = "K" Then Target.Value = "2019"
End If
[COLOR=#ff0000]'End new part[/COLOR]

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "G"

'   *** Specify start row ***
    myStartRow = 17
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
MAIT
Thanks very much i see what you have done and works great.

Can i ask for a piece of extra code to add to it.

Currently the code is set up for A-K so if i were to enter any other letter into the cell & hit enter i would like a msg box shown saying not found & the B13 cell to remain empty where currently it shows the letter which isnt supported.

Many thanks for this.
Currently i have this non working attempt
Code: 
'New partIf Not Intersect(Target, Range("B13")) Is Nothing Then
If UCase(Target.Value) = "A" Then Target.Value = "2010"
If UCase(Target.Value) = "B" Then Target.Value = "2011"
If UCase(Target.Value) = "C" Then Target.Value = "2012"
If UCase(Target.Value) = "D" Then Target.Value = "2013"
If UCase(Target.Value) = "E" Then Target.Value = "2014"
If UCase(Target.Value) = "F" Then Target.Value = "2015"
If UCase(Target.Value) = "G" Then Target.Value = "2016"
If UCase(Target.Value) = "H" Then Target.Value = "2017"
If UCase(Target.Value) = "I" Then Target.Value = "2018"
If UCase(Target.Value) = "J" Then Target.Value = "2019"
Else
MsgBox ("NOT FOUND")
End If
'End new part


End Sub
 
Last edited:
Upvote 0
Try this:

Code: 
If Not Intersect(Target, Range("B13")) Is Nothing Then
Dim x As Long
x = 0
Application.EnableEvents = False
If UCase(Target.Value) = "A" Then Target.Value = "2010": x = 1
If UCase(Target.Value) = "B" Then Target.Value = "2011": x = 1
If UCase(Target.Value) = "D" Then Target.Value = "2012": x = 1
If UCase(Target.Value) = "E" Then Target.Value = "2013": x = 1
If UCase(Target.Value) = "F" Then Target.Value = "2014": x = 1
If UCase(Target.Value) = "G" Then Target.Value = "2015": x = 1
If UCase(Target.Value) = "H" Then Target.Value = "2016": x = 1
If UCase(Target.Value) = "I" Then Target.Value = "2017": x = 1
If UCase(Target.Value) = "J" Then Target.Value = "2018": x = 1
If UCase(Target.Value) = "K" Then Target.Value = "2019": x = 1
If x < 1 Then MsgBox Target.Value & "  Not Found": Target.Value = ""
End If
Application.EnableEvents = True
 
Upvote 0
Hi,
It works fine but one thing that happens is when i close the worksheet i then get the msg box pop up.

If it helps i added the following into ALT + F11 This workbook to clear the cell as opposed to leaving a letter in it.

Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)With Sheets("HONDA SHEET").Range("B13").ClearContents
End With
End Sub
 
Last edited:
Upvote 0
If it works for you that's great. You never mentioned wanting to clear contents. Only if value did not equal those mentioned in the script.
 
Upvote 0
@ipbr21054
This should prevent the msgbox from appearing
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Sheets("HONDA SHEET").Range("B13").ClearContents
Application.EnableEvents = True
End Sub
 
Upvote 0
MAIT,
I didnt mention it because it was working fine, Its now sorted thanks.

Fluff, it works a treat.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
208
Skyybot
Skyybot
J
  • Solved
VBA - multiple filters from clicking different cells
2
Replies
11
Views
367
jh9940
J
M
  • Question Question
VBA macro required to change tab name based on cell value
Replies
4
Views
574
mumps
mumps
L
  • Question Question
VBA: Multiple SelectionChange Events
Replies
2
Views
247
Micron
M
D
  • Question Question
VBA to lock Cell after Data Entry but still Allow Sort & Filter ?
Replies
3
Views
274
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top