Excel Data Validation Combo box Click RRS feed

  • Question

  • I am very new to coding...

    Sheet 1 has my data validation in a scroll list on it and sheet 2 has the list that it is validating the data from. I am trying to make a combo box on sheet 1 that will auto fill while you type instead of having to type the exact name. The code below only works if the data list is on the same sheet as the sheet I am trying to make the combo box on. Any idea how to change the code so it will pull from sheet 2 where all the list of data is located?

    Any help would be greatly appreciated

    (I get the code from )

    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("TempCombo")
      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
        'open the drop down list automatically
      End If
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Exit Sub
      Resume exitHandler 
    End Sub 
    'Optional code to move to next cell if Tab or Enter are pressed
    'from code by Ted Lanham
    '***NOTE: if KeyDown causes problems, change to KeyUp
    Private Sub TempCombo_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

    Sunday, July 3, 2016 11:24 PM


  • Hi Lrstreet,

    >> .ListFillRange = ws.Range(str).Address

    If you want to pull from sheet2, you could change above line, use the Address in Sheet2.

    A simple demo like below:

    .ListFillRange = ActiveWorkbook.Worksheets("Sheet2").Range(str).Address

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 4, 2016 5:04 AM