Watch for linewrap. This should get you started. I left in some
options (commented out) that you can try. Not sure what your goal is.
Search Google Groups for msocontrolcombobox Excel for more ideas.
----------------------------------------------------------------------------------------------------------
Sub AddComboBox()
'' Places the ComboBox on the shortcut menu.
Dim cbCBO As CommandBarComboBox
Dim intI As Integer
Dim rngFill As Range
Dim varFill As Variant
' Set rngFill = Range("A1:A10") ' Use this for list of names in a
range.
' varFill = rngFill.Value
varFill = Array("First", "Second", "Third", "Fourth", "Fifth", _
"Sixth", "Seventh", "Eighth", "Ninth", "Tenth")
Set cbCBO =
CommandBars("Cell").Controls.Add(Type:=msoControlComboBox)
With cbCBO
.Caption = "Please Select"
For intI = LBound(varFill) To UBound(varFill)
.AddItem varFill(intI)
' .AddItem varFill(intI, 1)
Next intI
.Style = msoComboNormal
.OnAction = "Hello"
.Tag = "__This Combo__"
End With
End Sub
Sub DeleteComboBox()
' Deletes the combobox.
CommandBars("Cell").FindControl(Tag:="__This Combo__").Delete
End Sub
Sub Hello()
' Displays a MsgBox of nacros to run.
Dim cbCBO As CommandBarComboBox
Dim intIndex As Integer
Dim strIndex As String
' Set cbCBO = CommandBars("Cell").FindControl(Tag:="__This
Combo__")
intIndex = CommandBars("Cell").FindControl(Tag:="__This
Combo__").ListIndex
' Select Case cbCBO.List(cbCBO.ListIndex) ' This is a string. So
Case "First, Case "Second"
Select Case intIndex
Case 1: strIndex = "FirstMacro"
Case 2: strIndex = "SecondMacro"
Case 3: strIndex = "ThirdMacro"
Case 4: strIndex = "FourthMacro"
Case 5: strIndex = "FifthMacro"
Case 6: strIndex = "SixthMacro"
Case 7: strIndex = "SeventhMacro"
Case 8: strIndex = "EighthMacro"
Case 9: strIndex = "NinthMacro"
Case 10: strIndex = "TenthMacro"
Case Else:
End Select
MsgBox "You are about to run the " & strIndex & "."
CommandBars("Cell").FindControl(Tag:="__This Combo__").ListIndex = 0
End Sub
Tested using Excel 97SR2 on Windows 98SE,
HTH
Paul
