moving from menubars to commandbars 1

[GVF]

I have a large Excel Add-In with a custom menubar. The menubar has between 7 and 10 menus depending on options chosen elsewhere. There are a few submenus sprinkled within the main menus. On-Actions and captions change according to options chosen elsewhere.

All references to the MenuBar use the MenuBars object.

With Menubars("My Bar").menus("File").menuitems("Save").enabled......etc.

The MenuBars object seems to be on its way out. How critical is it that I re-write the MenuBar code to CommandBar code? (Which by the way I am struggling with)

Thanks,
Greg

 

[asjeff]

Ok - The help files can sometimes seem a bit daunting if you're a bit new at it, but it's actually quite easy. Whilst I can't help you with regards to your options, I have attached some code below that shows you how to create a simple CommandBar:

In Excel I find toolbars a real bind compared to the ease of MS Word toolbars, so I always destroy previous instances & recreate it when you open Excel, rather than trying to get it to save in the Personal.xls.

This example is called from an Autoexec routine

'##############################################
Sub AutoExec

Dim cbrNewBar As CommandBar
Dim cbcButtonA As CommandBarButton
Dim cbcButtonB As CommandBarButton

'Destroy previous instance
If Application.CommandBars("New Toolbar").Visible = True Then
Application.CommandBars("New Toolbar").Delete
End If

'recreate, name & position toolbar
Set cbrNewBar = Application.CommandBars.Add
cbrNewBar.Name = "New Toolbar"
cbrNewBar.Position = msoBarTop

'add buttons
With cbrNewBar.Controls
'set what type you want them to be
Set cbcButtonA = .Add(msoControlButton)
Set cbcButtonB = .Add(msoControlButton)

'add their captions
cbcButtonA.Caption = "Button A"
cbcButtonB.Caption = "Button B"

'tooltip text
cbcButtonA.DescriptionText = "Click to run Macro A"
cbcButtonB.DescriptionText = "Click to run Macro B"

'set their style (text/picture etc)
cbcButtonA.Style = msoButtonCaption
cbcButtonB.Style = msoButtonCaption

'add separator
cbcButtonB.BeginGroup = True

'specify macro that runs when you click them
cbcButtonA.OnAction = "MacroA"
cbcButtonB.OnAction = "MacroB"

End With

'make sure you can see it
cbrNewBar.Visible = True

End Sub
'####################################################

A very basic example with ounly 2 buttons but I hope this helps. Obviously you are going to have to do some work with it to get your options sorted, but it should give you a start. As for the criticalness of rewriting your menubar routines, I'm no expert but I suspect that they will still be compatible for a while yet.

Cheers

Asjeff

 

[GVF]

Thanks for taking the time to explain.

My add-in started out in the Excel 4.0 macro language and that still works so I hope microsoft holds on to Menubars for a bit longer.
By the way, in the Excel 4.0 macro language my menubar code was about 100 instructions. In VBA its over 500 lines of code.
Using commandbars I can see about 1500 lines of code.

Greg

 

[asjeff]

hmmm - i'm guessing you're going to be quite busy!! Sorry Greg - there will be ways of keeping the code more concise than the example above, but until you start it they probably wont be obvious, and I can't help you there.

Asjeff

 

[littlewoman]

Using Excel 2003-vba

Searching for a way to connect a customized controlbar to a specific workbook, instead of Excel in general, I stumbled on this code a few days ago.

I've copied it entirely placed it in a new module and linked to it from workbook_open
It works perfectly.

Yet wanting more as always hihihi I got stuck

And stuck on this code
'set what type you want them to be
Set cbcButtonA = .Add(msoControlButton)
Set cbcButtonB = .Add(msoControlButton)

if I change msoControlButton to msoControlDropDown there is indeed button placed like ie font
yet I want the buttonstyle dropdown like ie file
so I changed msoControlButton to msoControlButtonDropDown
but now I get

Run-time error '5':
invalid procedure call or argument

I can't understand it why dropdown works and buttondropdown doesn't evenmore since you can place it manually.

So plse can anyone tell me why it won't work and what will?

 

[shetlandbob]

See FAQ for how to insert your own menu?



Robert Cumming

http://www.consultantengineer.co.uk