CommandBarComboBox : AddItem - procedure to attach routine.

K

Kulin

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
 
J

JLGWhiz

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?
 
T

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
 
K

Kulin

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
 
K

Kulin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top