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
"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:="myCboButton").Text
> Case "Item 1"
> ' Do Whatever
> Case "Item 2"
> ' Do something else
> End Select
> End Select
>
>
>
> --
> Tim Zych
> SF, CA
>
>
> "Kulin" <(E-Mail Removed)> wrote in message
> news:E5C8C9EF-B145-448A-B3C1-(E-Mail Removed)...
> > 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
> >
> >
>
>
>
|
Similar Threads
