Excel - Dependent Data Validation ComboBox on Single Click

Question

I have code on a Worksheet that currently uses the BeforeDoubleClick. This code places a combobox over any cell that is double clicked that contains a data validation formula. The combobox takes on the formula.

I like this because you get the data validation list, but you also have the option for the user to type in the combobox and get autocomplete for the data validation options.

The worksheet itself has two data validation areas, one for State, one for City. The City data validation is dependent upon the selection for the State.

I really dislike that the user would have to double click every time they want to change the selection. It would be nice if they could single click and have it pop open.

Any help is appreciated!

Worksheet Code: (From http://www.contextures.com/xlDataVal11.html#works) ((They have another link (http://www.contextures.com/xlDataVal14.html) showing how to do this on single click, but when I try to add in the section for the =INDIRECT (which allows the dependent data validation) it doesnt work.))

'==========================
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

Set cboTemp = ws.OLEObjects("TempCombo")
  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
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)

    'for simple INDIRECT function (English)
    ' e.g. =INDIRECT(B2)
    'will create dependent list of items
    If Left(str, 4) = "INDI" Then
      lSplit = InStr(1, str, "(")
      str = Right(str, Len(str) - lSplit)
      str = Left(str, Len(str) - 1)
      str = Range(str).Value
    End If

    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 4
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
  End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub TempCombo_LostFocus()
  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
'====================================
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

mitchmitch24 mitchmitch24 · Accepted Answer · 2017-11-29T13:33:45

Wow, the answer was so simple...

I changed it to "Worksheet_SelectionChange(ByVal Target as Range)" (getting rid of the Cancel as Boolean) And then commented out the line "Cancel = True"

Hope this helps someone down the line!

Excel - Dependent Data Validation ComboBox on Single Click

1 Answers