Trigger event with drop down form button

[YasserKhalil]

Hello everyone
I have drop down form control on a worksheet with the name [myDropDown]. How can I trigger the event of selecting an item from it, displaying the item selected
I tried this but doesn't work for me

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myDrop As DropDown
    Set myDrop = Me.DropDowns("myDropDown")
    If Not Intersect(Target, myDrop.TopLeftCell) Is Nothing Then
        MsgBox "Selected value: " & myDrop.ControlFormat.List(myDrop.ControlFormat.Value)
    End If
End Sub

[HansV]

You have to assign a macro to the combo box. This must be a 'normal' macro in a standard module.

[YasserKhalil]

Thanks a lot. I already did that idea.
But is there OnAction property for drop-down form control?

[HansV]

All shapes have an OnAction property. It returns or sets the name of the macro assigned to the shape.

Code: Select all

    ActiveSheet.Shapes("myDropDown").OnAction = "myMacro"

[robertocm]

Sorry if i'm not understanding, but this would be an example:

Code: Select all

Private Sub ComboBoxIBAN_Change()
On Error GoTo ExceptionHandling
Application.EnableEvents = False

Dim IBAN As String, IdAr() As Variant, i As Integer
IBAN = Me.ComboBoxIBAN.value
IdAr = Application.Transpose(Sheets("DATOS").ListObjects("TB_IBAN").ListColumns("IBAN").DataBodyRange)
For i = 1 To UBound(IdAr)
    If IdAr(i) = IBAN Then
        With Sheets("DATOS").ListObjects("TB_IBAN").ListColumns("Elegir_M303")
            .DataBodyRange.ClearContents
            .DataBodyRange(i) = 1
        End With
    End If
Next i

CleanUp:
    On Error Resume Next
    Application.EnableEvents = True
    Exit Sub
ExceptionHandling:
    MsgBox "Error: " & Err.Description
    Resume CleanUp
    Resume 'for debugging
End Sub
Private Sub ComboBoxIBAN_DropButtonClick()
If Me.ComboBoxIBAN.ListCount > 0 Then Exit Sub
'populate ComboBox
Dim IdAr() As Variant
IdAr = Application.Transpose(Sheets("DATOS").ListObjects("TB_IBAN").ListColumns("IBAN").DataBodyRange)
Me.ComboBoxIBAN.List = IdAr
End Sub

Or using LostFocus event with the Value property (sorry for not having time now to write a general example):

Code: Select all

Private Sub ComboBox1_LostFocus()
On Error GoTo ExceptionHandling
Application.EnableEvents = False

With Me
    .Range("A11").Value = .ComboBox1.Value
End With

CleanUp:
    On Error Resume Next
    Application.EnableEvents = True
    Exit Sub
ExceptionHandling:
    'MsgBox "Error: " & Err.Description
    Resume CleanUp
    Resume 'for debugging
End Sub
Private Sub ComboBox1_GotFocus()
Me.ComboBox1.Value = ""
'ComboBox1.SelStart = 0
'ComboBox1.SelLength = Len(ComboBox1.text)
'CreateObject("WScript.Shell").SendKeys "{DELETE}", True
End Sub
Private Sub ComboBox1_DropButtonClick()
'http://stackoverflow.com/questions/31660393/excel-combo-box-refresh-dropdown
'Getting the list to ONLY show values that matched the text typed by the user ...
'Note that the MacthEntry Property of the combo box MUST be set to "2 - frmMatchEntryNone" for the code to work.
'(Other values cause the combo box .value property store the text of the first value that matches what the user typed, and the code relies on it storing what they typed.)
'Also, the code will pick up any items on the list that have the letters typed by the user ANYWHERE in their text.
''refresh the list
''Dim ValidValues() As Variant
''ValidValues = Worksheets("CLIENTES").Range("Client_TB[Nombre]").Value
''Me.ComboBox_SiteName.List = ValidValues

'populate combobox
'Antes usaba a propiedade ListFillRange, pero había que pechar arquivo para actualizar listas
Dim tbl As ListObject, ESP As Boolean
Set tbl = Sheets("CLIENTES").ListObjects("Client_TB")
Dim IdArr As Variant, IdArr2 As Variant
IdArr = Application.Transpose(tbl.ListColumns("Nombre").DataBodyRange)
IdArr2 = Application.Transpose(tbl.ListColumns("IVA").DataBodyRange)
If Me.OptionButtonESP.Value = True Then ESP = True
Dim y As Long, i As Integer, myCount As Integer
y = UBound(IdArr)

If ESP = True Then
  ReDim a_Es(1 To y)
  For i = 1 To y
  If IdArr2(i) = 1 Then
    myCount = myCount + 1
    a_Es(myCount) = IdArr(i)
  End If
  Next
  ReDim Preserve a_Es(1 To myCount)
  
  'Me.OLEObjects("Combobox1").Object.List = a_Es
  With Me.ComboBox1
    .List = Filter(a_Es, .Value, True, vbTextCompare)
  End With
Else
  ReDim a_Pt(1 To y)
  For i = 1 To y
  If IdArr2(i) = 0 Then
    myCount = myCount + 1
    a_Pt(myCount) = IdArr(i)
  End If
  Next
  ReDim Preserve a_Pt(1 To myCount)
    
  'Me.OLEObjects("Combobox1").Object.List = a_Es
  With Me.ComboBox1
    .List = Filter(a_Pt, .Value, True, vbTextCompare)
  End With
End If
End Sub

[YasserKhalil]

This is the most effective solution

You have to assign a macro to the combo box. This must be a 'normal' macro in a standard module.

Thank you very much, Mr. Hans

[robertocm]

This is the most effective solution

Ok, i see your question was about Form controls and i was thinking in ActiveX Controls, that cannot be directly assigned to a macro (according to the cited reference).

[YasserKhalil]

Thanks a lot for sharing