Assign Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

How do I assign three seperate macros to three seperate
item in an embeded combobox (from the FORMS toolbar). The
should be
Yes Macro 1
No Macro 2
Maybe Macro three

It seems that asigning macros to the combobox from
control toolbar is easier but I need to have this from
the FORMS toolbar for some strange reasons.

Your help is appreciated. Thanks in advance.
 
Hi All,

How do I assign three seperate macros to three seperate
item in an embeded combobox (from the FORMS toolbar). The
should be
Yes Macro 1
No Macro 2
Maybe Macro three

It seems that asigning macros to the combobox from
control toolbar is easier but I need to have this from
the FORMS toolbar for some strange reasons.

Your help is appreciated. Thanks in advance.
Click to expand...


Option Explicit
Public Sub FormsCombo()
Dim cb, ob, ct As Object
For Each cb In CommandBars
If cb.Name = "Forms" Then
Set ob = cb
Exit For
End If
Next
Set ct = ob.Controls.Add(Type:=msoControlComboBox, ID:=1)
With ct
.AddItem "Yes", 1
.AddItem "No", 2
.AddItem "Maybe", 3
.DropDownLines = 5
.DropDownWidth = 100
.ListHeaderCount = 0
.OnAction = "Combo_Click"
End With
ob.Visible = True
End Sub
Public Sub Combo_Click()
With Application.CommandBars.ActionControl
MsgBox .Text
End With
End Sub
 
anon,

You can assign a macro to the combo box. The macro will be fired whenever
the combo box has been changed. You'll need to set up a linked cell in the
combo box, which the macro can look at for 1, 2, or 3, and launch the
respective macro.

Sub ComboBoxChange
Select Case Range("ComboBoxLinkedCell")
Case is = 1 ' Yes (first item) selected
Macro1
Case is = 2 ' second item selected
Macro2
Case is = 2
Macro3
End Select
End Sub

Untested
 
You can get the ListIndex of the selected item, and run a different
macro for each list item. For example, with your sample list, Yes is in
position 1 in the dropdown list, No is 2, and Maybe is 3:

'=============================
Sub DropDown1_Change()
Dim i As Integer
i = Sheets("Sheet1").DropDowns(1).ListIndex

Select Case i
Case 1
YesCode
Case 2
NoCode
Case 3
MaybeCode
End Select

End Sub
'=======================
 
Earl and Debra showed a way using the linked cell, but you can use the list
directly, too:

Option Explicit
Sub testme()
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
If .ListIndex > 0 Then
Select Case LCase(.List(.ListIndex))
Case Is = "yes": Call YesCode
Case Is = "no": Call NoCode
Case Is = "maybe": Call MaybeCode
End Select
End If
End With

End Sub
Sub YesCode()
MsgBox "Yes"
End Sub
Sub NoCode()
MsgBox "No"
End Sub
Sub MaybeCode()
MsgBox "Maybe"
End Sub

====
And if you have multiple dropdowns that need this same behavior, you can assign
them all this same macro.
 
er...

Debra used .listindex.

I guess I meant to say that you can use the value that appears in the dropdown
directly, too.
 
Back
Top