ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   CommandBarComboBox : AddItem - procedure to attach routine. (https://www.excelbanter.com/excel-programming/400915-commandbarcombobox-additem-procedure-attach-routine.html)

Kulin
CommandBarComboBox : AddItem - procedure to attach routine.
 
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

JLGWhiz
CommandBarComboBox : AddItem - procedure to attach routine.
 
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?

"Kulin" wrote:

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

Tim Zych
CommandBarComboBox : AddItem - procedure to attach routine.
 
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:="myCboBut ton").Text
Case "Item 1"
' Do Whatever
Case "Item 2"
' Do something else
End Select
End Select



--
Tim Zych
SF, CA


"Kulin" wrote in message
...
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

Kulin
CommandBarComboBox : AddItem - procedure to attach routine.
 
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).Lis tIndex)
Worksheets(sheetName).Select
End Sub


kulin

"Tim Zych" wrote:

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:="myCboBut ton").Text
Case "Item 1"
' Do Whatever
Case "Item 2"
' Do something else
End Select
End Select



--
Tim Zych
SF, CA


"Kulin" wrote in message
...
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

Kulin
CommandBarComboBox : AddItem - procedure to attach routine.
 
Thanks. I did like this:

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

kulin


"JLGWhiz" wrote:

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?

"Kulin" wrote:

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


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com