Pop up drop down menu

[William Kiarie]

Hi team,

I'm imagining if I can create a vba code such that when I left-click in a cell, the list pops up but when I click outside the given cell the menu disappears (more or less the same way data validation behaves).

Any guidance highly appreciated.

 

[Luke M]

Hi William,

I'd use 90% of the method described here:
http://www.contextures.com/xlDataVal10.html

You'll need to build a combobox, set it's properties, and do some prep work.

In Debra's example, she uses the Double-click event and Selection events. For your case, we just want the selection event. After you get the combo box setup, right-click on sheet tab, view code, paste this in:

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'First, clear out from previos selection
Set cboTemp = Me.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 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 = Me.Range(str).Address
      .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

 

[William Kiarie]

Luke,

Much appreciated. Are you able to upload a file that uses the above code. I've tried copy pasting the same on a blank sheet (after I view code) but doesn't seem to get it.

Thank you.

 

[Luke M]

Sure thing.