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!
|
Similar Threads
