Monday, May 07, 2007

Add Combo Box to the command Bar

Add Combo Box to the command Bar

Sub Show_Combo_CommandBar()


Dim oCB As CommandBar
Dim oCtl As CommandBarComboBox

On Error Resume Next

'Delete Control From CommandBar
CommandBars("Sample Command Bar").Delete

Set oCB = CommandBars.Add
oCB.Name = "Sample Command Bar"
oCB.AdaptiveMenu = True

'Add Control to CommandBar
Set oCtl = oCB.Controls.Add(Type:=msoControlComboBox)
oCtl.Caption = "ComboSamp"

'Link Macro to CommandBar,
oCtl.OnAction = "Change_Header_Background"

'Add list Item to Combo Box Control
oCtl.AddItem "NoColor"
oCtl.AddItem "Blue"
oCtl.AddItem "Yellow"


' Show the Command Bar
oCB.Visible = True

' Place the CommandBar at the bottom of the screen
oCB.Position = msoBarBottom

End Sub

Sub Change_Header_Background()

' Acts based on the value in the Combo Box

Dim oCB As CommandBar
Dim oCtl As CommandBarComboBox

On Error Resume Next


Set oCB = CommandBars("Sample Command Bar")

Set oCtl = oCB.Controls("ComboSamp")

If oCtl.ListIndex <> -1 Then

Select Case oCtl.ListIndex

Case 1

ActiveSheet.Rows(1).Interior.ColorIndex = 0

Case 2

ActiveSheet.Rows(1).Interior.ColorIndex = 5

Case 3

ActiveSheet.Rows(1).Interior.ColorIndex = 36

Case Else

' Do nothing

End Select

End If


' Show the Command Bar
oCB.Visible = True

' Place the CommandBar at the bottom of the screen
oCB.Position = msoBarBottom


End Sub

2 comments:

  1. How would I go about referencing this combo box in a formula once it is created in the toolbar?

    Any help would be much appreciated!

    ReplyDelete
  2. Anonymous2:00 PM

    Hi i think you would create a function that returns the value of the combo box, and then refer to that function in the excel formula. i have not done this before so can only offer some pseudo code:

    VBA function:
    function ComboValue()
    ComboValue =
    end function

    Excel formula:
    =ComboValue()

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.