make the combo box appear when you click in a cell that contains a data validation list RRS feed

  • Question

  • I have this code to make  the combo box appear when you click in a cell that contains a data validation list.

    I need to use it with a more than one column combobox list because right now i only can use it with a one column list.

    The code is :

    Private Sub TempCombo_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        'Hide combo box and move to next cell on Enter and Tab
        Select Case KeyCode
            Case 9
                ActiveCell.Offset(0, 1).Activate
            Case 13
                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("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
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          .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
      End If

      Application.EnableEvents = True
      Application.ScreenUpdating = True
      Exit Sub
      Resume exitHandler

    End Sub

    • Edited by fayo_0102 Wednesday, November 26, 2014 7:21 PM
    Wednesday, November 26, 2014 7:21 PM

All replies

  • Hello,

    I can't reproduce your problem as there're some errors in your VBA code, I recommend that you upload a sample Excel file in OneDrive so that I can test it and give you some suggestions.

    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.

    Thursday, November 27, 2014 8:01 AM