Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / December 2007

Tip: Looking for answers? Try searching our database.

Ambiguous Name detected?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
davegb - 07 Dec 2007 16:44 GMT
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.
Jim Thomlinson - 07 Dec 2007 16:59 GMT
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...
Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 134 lines]
> "ambiguous", but didn't find anything that seemed relevant. Any ideas
> would be greatfully accepted.
davegb - 07 Dec 2007 17:09 GMT
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
[quoted text clipped - 145 lines]
>
> - 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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.