CommandBarComboBox : AddItem - procedure to attach routine.

[Guest]

Question :
=======
Exact syntax required when clicking/selecting AddItem 1 , I can invoke the
following Sub ShowExpenses()
Worksheets("Data").Select
End Sub


, on selecting AddItem 2, I can invoke the

Sub ShowSales()
Worksheets("Facility").Select
End Sub

and on selecting AddItem 3, I can invoke the following

Sub ShowPurchases()
Worksheets("Segment").Select
End Sub


Bye the way , I am little bit novice for VBA-EXCELL.





My entire procedure start from here :
========================



Sub CreateNewToolBar()
'the next two lines are only required during development
On Error Resume Next
CommandBars("Accounts").Delete

Dim NewMenuBar As CommandBar
Dim NewButton As CommandBarButton

Set NewMenuBar = CommandBars.Add("Accounts")

Set NewButton = NewMenuBar.Controls.Add(msoControlButton,
CommandBars("View").Controls("Normal").ID)
NewButton.Caption = "&Normal"
NewButton.Style = msoButtonIconAndCaptionBelow

Set NewButton = NewMenuBar.Controls.Add(msoControlButton,
CommandBars("View").Controls("Page Break Preview").ID)
NewButton.Caption = "&Preview"
NewButton.Style = msoButtonIconAndCaption

Set NewButton = NewMenuBar.Controls.Add(msoControlButton)
NewButton.Caption = "&Data"
NewButton.Style = msoButtonCaption
NewButton.OnAction = "ShowExpenses"

Dim NewComboboxButton As CommandBarComboBox
Set NewComboboxButton = NewMenuBar.Controls.Add(msoControlComboBox)
NewComboboxButton.Caption = "&Segment"
NewComboboxButton.OnAction = "ShowPurchases"
With CommandBars("Accounts").Controls(4)
.AddItem "Item 1", 1
.AddItem "Item 2", 2
.AddItem "Item 3", 3
.DropDownLines = 3
.ListIndex = 1
End With



Set NewButton = NewMenuBar.Controls.Add(msoControlButton)
NewButton.Caption = "&Facility"
NewButton.Style = msoButtonCaption
NewButton.OnAction = "ShowSales"

Set NewButton = NewMenuBar.Controls.Add(msoControlButton)
NewButton.Caption = "&E&xit"
NewButton.OnAction = "RestoreExcelMenuBar"
NewButton.Style = msoButtonCaption

Worksheets("AccountsSheet").Select
NewMenuBar.Visible = True
End Sub

Sub ShowExpenses()
Worksheets("Data").Select
End Sub

Sub ShowPurchases()
Worksheets("Segment").Select
End Sub

Sub ShowSales()
Worksheets("Facility").Select
End Sub

Sub RestoreExcelMenuBar()
CommandBars("Accounts").Delete
Application.Quit
End Sub

 

[Guest]

Are AddItem 1 and AddItem 2 entries in your combo box list, or do you have
two combo boxes with these names. Also, did you create the combo box(es)
from the Forms toolbar or the Control Toolbox toolbar?

 

[Tim Zych]

I haven't replicated your environment...but this is the general idea.

In the creation of the combobox control, assign a Tag to it. The
OnAction/called macro ("ShowPurchases") evaluates whether the combo was
clicked. Then you figure out the value in the control and run whatever
action you need.

Add this to the part that creates the combobox control:
NewComboboxButton.Tag = "myCboButton"

Then in the "ShowPurchases" macro, add to your existing macro:

Select Case Application.CommandBars.ActionControl.Tag
Case "myCboButton"
Select Case
Application.CommandBars.FindControl(Tag:="myCboButton").Text
Case "Item 1"
' Do Whatever
Case "Item 2"
' Do something else
End Select
End Select

 

[Guest]

Thanks. I will try.

In the meantime, I did like this.

Sub ShowPurchases()
Dim sheetName as string
sheetName=Array(vbNullChar, "Data", "Facility",
"Segment")(CommandBars("Accounts").Controls(4).ListIndex)
Worksheets(sheetName).Select
End Sub


kulin

 

[Guest]

Thanks. I did like this:

Sub ShowPurchases()
Dim sheetName as string
sheetName=Array(vbNullChar, "Data", "Facility",
"Segment")(CommandBars("Accounts").Controls(4).ListIndex)
Worksheets(sheetName).Select
End Sub

kulin