Results 1 to 12 of 12

[SOLVED] compile error ambiguous name detected

Threaded View

truman40 [SOLVED] compile error... 02-16-2010, 09:58 AM
dominicb Re: compile error ambigous... 02-16-2010, 10:05 AM
truman40 Re: compile error ambigous... 02-16-2010, 05:25 PM
truman40 Re: compile error ambigous... 02-16-2010, 11:54 PM
truman40 Re: compile error ambigous... 02-18-2010, 09:19 AM
truman40 Re: compile error ambigous... 02-18-2010, 08:59 PM
Andy Pope Re: compile error ambiguous... 02-19-2010, 05:26 AM
truman40 Re: compile error ambiguous... 02-22-2010, 03:02 AM
Andy Pope Re: compile error ambiguous... 02-22-2010, 07:12 AM
truman40 Re: compile error ambiguous... 02-24-2010, 01:12 AM
Andy Pope Re: compile error ambiguous... 02-24-2010, 06:01 AM
truman40 Re: compile error ambiguous... 02-24-2010, 08:36 AM
  1. #1
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    [SOLVED] compile error ambiguous name detected

    Hello, Im using a script that allows me to autocomplete a data validation list using a combo box and the code shown below. However I am getting a compile error ambigous name detected and the following line is highlighted
    "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"

    I know its because this is used twice but I am not sure what to do in order to fix this. I would appreciate some help on this.

    Thank you.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
      Cancel As Boolean)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Cancel = True
    Set cboTemp = ws.OLEObjects("combobox1")
      On Error Resume Next
      With cboTemp
      'clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
      End With
    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Application.EnableEvents = False
        'get the data validation formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 5
          .Height = Target.Height + 5
          .ListFillRange = ws.Range(str).Address
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
      End If
      
    errHandler:
      Application.EnableEvents = True
      Exit Sub
    
    End Sub
    Private Sub combobox1_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        Select Case KeyCode
            Case 9 'Tab
                ActiveCell.Offset(0, 1).Activate
            Case 13 'Enter
                ActiveCell.Offset(1, 0).Activate
            Case Else
                'do nothing
        End Select
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error GoTo errHandler
    
    If Target.Count > 1 Then GoTo exitHandler
    
    Set cboTemp = ws.OLEObjects("combobox2")
      On Error Resume Next
    If cboTemp.Visible = True Then
      With cboTemp
        .Top = 10
        .Left = 10
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With
    End If
    
      On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Application.EnableEvents = False
        'get the data validation formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 15
          .Height = Target.Height + 5
          .ListFillRange = ws.Range(str).Address
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
      End If
    
    exitHandler:
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Exit Sub
    errHandler:
      Resume exitHandler
    
    End Sub
    Private Sub combobox2_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        Select Case KeyCode
            Case 9 'Tab
                ActiveCell.Offset(0, 1).Activate
            Case 13 'Enter
                ActiveCell.Offset(1, 0).Activate
            Case Else
                'do nothing
        End Select
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error GoTo errHandler
    
    If Target.Count > 1 Then GoTo exitHandler
    
    Set cboTemp = ws.OLEObjects("combobox3")
      On Error Resume Next
    If cboTemp.Visible = True Then
      With cboTemp
        .Top = 10
        .Left = 10
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With
    End If
    
      On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Application.EnableEvents = False
        'get the data validation formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 15
          .Height = Target.Height + 5
          .ListFillRange = ws.Range(str).Address
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
      End If
    
    exitHandler:
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Exit Sub
    errHandler:
      Resume exitHandler
    
    End Sub
    Private Sub combobox3_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        Select Case KeyCode
            Case 9 'Tab
                ActiveCell.Offset(0, 1).Activate
            Case 13 'Enter
                ActiveCell.Offset(1, 0).Activate
            Case Else
                'do nothing
        End Select
    End Sub
    
    
    
    Private Sub CommandButton1_Click()
    Range("C10").Select
        Selection.ClearContents
        Range("E14").Select
        Selection.ClearContents
        Range("G14").Select
        Selection.ClearContents
        Range("C12").Select
    End Sub
    Last edited by truman40; 02-16-2010 at 05:22 PM. Reason: Changed quote tags to code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1