get value from combobox on toolbar

[Dan]

Hi,
I have created a combobox on the drawimg toolbar (using code below - copied
from the Excel VBA help file).

How can I find what the selection on it, I would like to know if "First
Item" or "Second Item" is currently selected.


Many thanks,
Dan

Sub Craete_toolbar_Combo()
Set myControl = CommandBars("drawing").Controls _
.Add(Type:=msoControlComboBox, Before:=1)
With myControl
.AddItem Text:="First Item", Index:=1
.AddItem Text:="Second Item", Index:=2
.DropDownLines = 3
.DropDownWidth = 75
.ListHeaderCount = 0
End With
End Sub

 

[Dave Peterson]

I'd use something like:

Option Explicit
Sub Create_toolbar_Combo()
Dim myControl As CommandBarControl
Set myControl = Application.CommandBars("drawing").Controls _
.Add(Type:=msoControlComboBox, Before:=1)
With myControl
.AddItem Text:="First Item", Index:=1
.AddItem Text:="Second Item", Index:=2
.DropDownLines = 3
.DropDownWidth = 75
.ListHeaderCount = 0
.OnAction = "'" & ThisWorkbook.Name & "'!mymacro"
End With
End Sub
Sub mymacro()
With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please make a selection"
Exit Sub '???
Else
MsgBox .List(.ListIndex)
End If
End With
End Sub