Ambiguous Name detected?
 

I'm working on a macro that brings up a userform, gets the info from
the user, then goes back to the General Module to create a new
spreadsheet based on the user's input. I have the userform working
fine, but now I'm getting an "Ambigous Name detected" when I run the
macro. Here is the userform code:

Option Explicit

Private Sub btnCancel_Click()
End
End Sub

Private Sub btnTop21BOS_Click()

End Sub

Private Sub CheckBox1_Click()
bHdr = True
End Sub

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long
Dim s1stCtyName As String

If btnTop10BOS Then lTop = 10 <======= AMBIGUOUS NAME DETECTED
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

On Error Resume Next
Set rFirstData = Range(reDataStrt.Text)
If Err < 0 Then
MsgBox "Invalid Range Selected"
reDataStrt.SetFocus
On Error GoTo 0
Exit Sub
End If

Set rFndCell = rFirstData.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

If rFirstData Is Nothing Then
Exit Sub 'user hit cancel
End If

lLastCol = rFirstData.Columns(.Columns.Count).Column

If cbHdr = True Then bHdr = True

End If
uf1021Mid.Hide

End Sub

Here is the General Module code:

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range


Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet


Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST FOR SHEET NAMED "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If
Next

lTop = 0
bHdr = False

uf1021Mid.Show


End Sub

The General Module code doesn't do much yet, except do some error
trapping and display the userform.

When I get the error, it highlight the "lTop =", including the = sign,
as though VBA has forgotten what an = sign means. Very confused by
this, as I've done the same a hundred times. I think it probably has
something to do with the userform maybe? I did search this group for
"ambiguous", but didn't find anything that seemed relevant. Any ideas
would be greatfully accepted.

Ambiguous Name detected?
 

Normally you get an ambigous name when you have two sub procedures with the
same name or if you have a control with the same name as a sub procedure or
function. Basically somewhere you have two things with the same name... When
you try to refer to it the compiler has no idea which one...
--
HTH...

Jim Thomlinson


"davegb" wrote:

I'm working on a macro that brings up a userform, gets the info from
the user, then goes back to the General Module to create a new
spreadsheet based on the user's input. I have the userform working
fine, but now I'm getting an "Ambigous Name detected" when I run the
macro. Here is the userform code:

Option Explicit

Private Sub btnCancel_Click()
End
End Sub

Private Sub btnTop21BOS_Click()

End Sub

Private Sub CheckBox1_Click()
bHdr = True
End Sub

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long
Dim s1stCtyName As String

If btnTop10BOS Then lTop = 10 <======= AMBIGUOUS NAME DETECTED
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

On Error Resume Next
Set rFirstData = Range(reDataStrt.Text)
If Err < 0 Then
MsgBox "Invalid Range Selected"
reDataStrt.SetFocus
On Error GoTo 0
Exit Sub
End If

Set rFndCell = rFirstData.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

If rFirstData Is Nothing Then
Exit Sub 'user hit cancel
End If

lLastCol = rFirstData.Columns(.Columns.Count).Column

If cbHdr = True Then bHdr = True

End If
uf1021Mid.Hide

End Sub

Here is the General Module code:

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range


Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet


Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST FOR SHEET NAMED "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If
Next

lTop = 0
bHdr = False

uf1021Mid.Show


End Sub

The General Module code doesn't do much yet, except do some error
trapping and display the userform.

When I get the error, it highlight the "lTop =", including the = sign,
as though VBA has forgotten what an = sign means. Very confused by
this, as I've done the same a hundred times. I think it probably has
something to do with the userform maybe? I did search this group for
"ambiguous", but didn't find anything that seemed relevant. Any ideas
would be greatfully accepted.

Ambiguous Name detected?
 

On Dec 7, 9:59 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Normally you get an ambigous name when you have two sub procedures with the
same name or if you have a control with the same name as a sub procedure or
function. Basically somewhere you have two things with the same name... When
you try to refer to it the compiler has no idea which one...
--
HTH...

Jim Thomlinson



"davegb" wrote:
I'm working on a macro that brings up a userform, gets the info from
the user, then goes back to the General Module to create a new
spreadsheet based on the user's input. I have the userform working
fine, but now I'm getting an "Ambigous Name detected" when I run the
macro. Here is the userform code:

Option Explicit

Private Sub btnCancel_Click()
End
End Sub

Private Sub btnTop21BOS_Click()

End Sub

Private Sub CheckBox1_Click()
bHdr = True
End Sub

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long
Dim s1stCtyName As String

If btnTop10BOS Then lTop = 10 <======= AMBIGUOUS NAME DETECTED
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

On Error Resume Next
Set rFirstData = Range(reDataStrt.Text)
If Err < 0 Then
MsgBox "Invalid Range Selected"
reDataStrt.SetFocus
On Error GoTo 0
Exit Sub
End If

Set rFndCell = rFirstData.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

If rFirstData Is Nothing Then
Exit Sub 'user hit cancel
End If

lLastCol = rFirstData.Columns(.Columns.Count).Column

If cbHdr = True Then bHdr = True

End If
uf1021Mid.Hide

End Sub

Here is the General Module code:

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range

Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet

Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST FOR SHEET NAMED "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If
Next

lTop = 0
bHdr = False

uf1021Mid.Show

End Sub

The General Module code doesn't do much yet, except do some error
trapping and display the userform.

When I get the error, it highlight the "lTop =", including the = sign,
as though VBA has forgotten what an = sign means. Very confused by
this, as I've done the same a hundred times. I think it probably has
something to do with the userform maybe? I did search this group for
"ambiguous", but didn't find anything that seemed relevant. Any ideas
would be greatfully accepted.- Hide quoted text -

- Show quoted text -

Thanks for your reply, Jim. I started to reply that I had figured out
that much, but couldn't find any dupes. Then I remembered I had taken
a stab at this project before, and had started a new module, using
many of the same variable names, so I had 2 open modules with some of
the same variables publicly declared. I remarked out the others and
the problem is gone.

Thanks as always!