Question on "Programming a MenuBar"

  • Thread starter Thread starter David F. Schrader
  • Start date Start date
D

David F. Schrader

So far I have been able to determine how to create a
menubar and add a number of buttons and pop-ups
to it using VBA. That's all fine if all you want to do
is have a few tasks that can be done via a button.

Can someone send me a *simple* *clean* example
of how one adds buttons and pop-ups to a pop-up
that's on the menubar to form a "tree like" menu?
Something like:
[PopUp][[Button][Button][Button][Button]
[Button ]
[Button ]
[Popup ]---[Button]
[Button ] [PopUp]---[Button]
[Button ] [Button ] [Button]
[Button ] [Button ]
[Button ] [PopUp ]---[PopUp]---[Button]
[Button ] [Button ] [Button ] [Button ]
[Button ] [Button ] [Button ]

And so on.

Somehow, each popup has to know when a new level
is starting and when one is ending. Using the "with"
would seem logical but I can't find any examples that
show how it would be implemented.

Any and all help appreciated.

David Schrader
 
Const cCommandBar = "MyCommandBar"

Sub Toolbar_OFF()
Dim bar As CommandBar

''' Delete the Commandbar if it already exists
For Each bar In Application.CommandBars
If bar.Name = cCommandBar Then bar.Delete
Next
End Sub

Sub Toolbar_ON()
Dim bar As CommandBar

Toolbar_OFF

Set bar = Application.CommandBars.Add(Name:=cCommandBar,
Position:=msoBarTop, Temporary:=True)

''' Popup
With bar.Controls.Add(Type:=msoControlPopup)
.Caption = "Cards"
With .CommandBar.Controls.Add(Type:=msoControlPopup)
.Caption = "Red Cards"
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 481
.Caption = "Heart"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Heart"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 482
.Caption = "Diamond"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Diamond"
End With
End With
With .CommandBar.Controls.Add(Type:=msoControlPopup)
.Caption = "Black Cards"
With .CommandBar.Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.FaceId = 483
.Caption = "Spade"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Spade"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 484
.Caption = "Club"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Club"
End With
End With
End With

bar.Visible = True
End Sub

Sub Popup_Click()
With Application.CommandBars.ActionControl
MsgBox "You selected: " & .Parameter
End With
End Sub
 
You might also want to take a look at John Walkenbach's Menu Maker, it
simplifies life http://www.j-walk.com/ss/excel/tips/tip53.htm

--

HTH

RP
(remove nothere from the email address if mailing direct)


Rob van Gelder said:
Const cCommandBar = "MyCommandBar"

Sub Toolbar_OFF()
Dim bar As CommandBar

''' Delete the Commandbar if it already exists
For Each bar In Application.CommandBars
If bar.Name = cCommandBar Then bar.Delete
Next
End Sub

Sub Toolbar_ON()
Dim bar As CommandBar

Toolbar_OFF

Set bar = Application.CommandBars.Add(Name:=cCommandBar,
Position:=msoBarTop, Temporary:=True)

''' Popup
With bar.Controls.Add(Type:=msoControlPopup)
.Caption = "Cards"
With .CommandBar.Controls.Add(Type:=msoControlPopup)
.Caption = "Red Cards"
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 481
.Caption = "Heart"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Heart"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 482
.Caption = "Diamond"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Diamond"
End With
End With
With .CommandBar.Controls.Add(Type:=msoControlPopup)
.Caption = "Black Cards"
With .CommandBar.Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.FaceId = 483
.Caption = "Spade"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Spade"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 484
.Caption = "Club"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Club"
End With
End With
End With

bar.Visible = True
End Sub

Sub Popup_Click()
With Application.CommandBars.ActionControl
MsgBox "You selected: " & .Parameter
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


David F. Schrader said:
So far I have been able to determine how to create a
menubar and add a number of buttons and pop-ups
to it using VBA. That's all fine if all you want to do
is have a few tasks that can be done via a button.

Can someone send me a *simple* *clean* example
of how one adds buttons and pop-ups to a pop-up
that's on the menubar to form a "tree like" menu?
Something like:
[PopUp][[Button][Button][Button][Button]
[Button ]
[Button ]
[Popup ]---[Button]
[Button ] [PopUp]---[Button]
[Button ] [Button ] [Button]
[Button ] [Button ]
[Button ] [PopUp ]---[PopUp]---[Button]
[Button ] [Button ] [Button ] [Button ]
[Button ] [Button ] [Button ]

And so on.

Somehow, each popup has to know when a new level
is starting and when one is ending. Using the "with"
would seem logical but I can't find any examples that
show how it would be implemented.

Any and all help appreciated.

David Schrader
Click to expand...
Click to expand...
 
To Bob, Rob, and "bimble,"

(One has to wonder why MS had to make it
quite so complex to accomplish what should
be what appears on the surface to be a simple
task.)

My thanks. I think, between all of the material
that the three of you have provided - if I can
swim back to the surface - I can make it work.

David


Bob Phillips said:
You might also want to take a look at John Walkenbach's Menu Maker, it
simplifies life http://www.j-walk.com/ss/excel/tips/tip53.htm

--

HTH

RP
(remove nothere from the email address if mailing direct)


Rob van Gelder said:
Const cCommandBar = "MyCommandBar"

Sub Toolbar_OFF()
Dim bar As CommandBar

''' Delete the Commandbar if it already exists
For Each bar In Application.CommandBars
If bar.Name = cCommandBar Then bar.Delete
Next
End Sub

Sub Toolbar_ON()
Dim bar As CommandBar

Toolbar_OFF

Set bar = Application.CommandBars.Add(Name:=cCommandBar,
Position:=msoBarTop, Temporary:=True)

''' Popup
With bar.Controls.Add(Type:=msoControlPopup)
.Caption = "Cards"
With .CommandBar.Controls.Add(Type:=msoControlPopup)
.Caption = "Red Cards"
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 481
.Caption = "Heart"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Heart"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 482
.Caption = "Diamond"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Diamond"
End With
End With
With .CommandBar.Controls.Add(Type:=msoControlPopup)
.Caption = "Black Cards"
With .CommandBar.Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.FaceId = 483
.Caption = "Spade"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Spade"
End With
With .CommandBar.Controls.Add(Type:=msoControlButton)
.FaceId = 484
.Caption = "Club"
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = "Club"
End With
End With
End With

bar.Visible = True
End Sub

Sub Popup_Click()
With Application.CommandBars.ActionControl
MsgBox "You selected: " & .Parameter
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


David F. Schrader said:
So far I have been able to determine how to create a
menubar and add a number of buttons and pop-ups
to it using VBA. That's all fine if all you want to do
is have a few tasks that can be done via a button.

Can someone send me a *simple* *clean* example
of how one adds buttons and pop-ups to a pop-up
that's on the menubar to form a "tree like" menu?
Something like:
[PopUp][[Button][Button][Button][Button]
[Button ]
[Button ]
[Popup ]---[Button]
[Button ] [PopUp]---[Button]
[Button ] [Button ] [Button]
[Button ] [Button ]
[Button ] [PopUp ]---[PopUp]---[Button]
[Button ] [Button ] [Button ] [Button ]
[Button ] [Button ] [Button ]

And so on.

Somehow, each popup has to know when a new level
is starting and when one is ending. Using the "with"
would seem logical but I can't find any examples that
show how it would be implemented.

Any and all help appreciated.

David Schrader
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Screen position of commandbar button in Excel 2007 ? 11
How do I find what is in a spreadsheet 4
form buttons on sheet collapse on unhide - sometimes 5
How to enable "Links" when copy a worksheet (with buttons on it) to anew book 2
Access Automatically Send a notification email once a record is added 0
IPad remote 10
programmatically change icons on a menubar 2
user input recorded twice on excel sheet 7

Back
Top