Creating a Toolbar with a macro

  • Thread starter Thread starter DavidP
  • Start date Start date
D

DavidP

I have the following code which should create a command bar and add 17
buttons. I shortened the example to 3 buttons to save readers time.

Sub Workbook_Open()

'Delete CommandBar if it exists
On Error Resume Next
CommandBars("DataEntry").Delete
On Error GoTo 0

CommandBars.Add Name:="DataEntry"

With
Application.CommandBars("DataEntry").Controls.Add(temporary:=True)
.Caption = "Anti Virus"
.OnAction = "PasteAntiVirus"
End With

With
Application.CommandBars("DataEntry").Controls.Add(temporary:=True)
.Caption = "Audio"
.OnAction = "PasteAudio"
End With

With
Application.CommandBars("DataEntry").Controls.Add(temporary:=True)
.Caption = "Backup"
.OnAction = "PasteBackup"
End With

End Sub

When I open the spreadsheet the macro stops at the line

CommandBars.Add Name:="DataEntry"

The error message is "Run Time error '91' Object Variable ot With
Block Variable not set

I have looked through previous postings but none seem relevant to my
problem. The macro runs when I open the spreadsheet. My XL version
is 2000 running on Win98.

Can anyone point me in the right direction please

David P
 
David,

I guess the problem must be in some other code. I have tried it half a dozen
times (XP Pro, XL2000) and it works every time.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi David,

To be honest, I'm not quite sure why this error is occuring (it's late,
so maybe I'll think of it in the morning <g>), but it can be cured by
following one of the central best practices of VBA programming, which is to
fully qualify all your references. You've done this in most cases by
specifying the Excel Application object, but not in your CommandBars.Add
statement. Changing this statement to:

Application.CommandBars.Add Name:="DataEntry"

clears up the error for me. I assume it has something to do with confusion
about who "owns" the CommandBars keyword at startup, since it's both an
Office object and an Excel Application method that returns an
Office.CommandBars object.

In addition, you can clean up the code a bit by declaring a variable to
hold a reference to the command bar you're creating, like so:

Private Sub Workbook_Open()

Dim objBar As Office.CommandBar

'Delete CommandBar if it exists
On Error Resume Next
Application.CommandBars("DataEntry").Delete
On Error GoTo 0

Set objBar = Application.CommandBars.Add("DataEntry")

With objBar.Controls.Add(temporary:=True)
.Caption = "Anti Virus"
.OnAction = "PasteAntiVirus"
End With

With objBar.Controls.Add(temporary:=True)
.Caption = "Audio"
.OnAction = "PasteAudio"
End With

With objBar.Controls.Add(temporary:=True)
.Caption = "Backup"
.OnAction = "PasteBackup"
End With

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Thanks for your help gents. The changes Rob suggested did the trick
and the code runs fine. One further problem for you now.

The code creates the toolbar and I have adapted the Macro to make it
visible but all the icons are blank so the command bar appears blank.
When I hover over the different areas the name appears but it is not
very friendly to say the least. Is there any way I can allocate
icons, preferably predefined ones, but anything better than nothing?

If this is not possible then I will revert to Plan B which is to
create the button manually and just use the macros to Display/Hide the
"DataEntry" command Bar. I had started out by doing this but the
Toolbar kept disappearing and recreating it is a major task since
there are 17 buttons to be created/edited etc

Thanks DavidP
 
Hi David,

Yes, this is very easy to do. You simply need to assign a value to the
FaceID property of each of your toolbar buttons. Here's an example based on
the previous code we were looking at:

Sub Test()

Dim objBar As Office.CommandBar

'Delete CommandBar if it exists
On Error Resume Next
Application.CommandBars("DataEntry").Delete
On Error GoTo 0

Set objBar = Application.CommandBars.Add("DataEntry")

With objBar.Controls.Add(temporary:=True)
.Caption = "Anti Virus"
.OnAction = "PasteAntiVirus"
.FaceId = 2520
End With

With objBar.Controls.Add(temporary:=True)
.Caption = "Audio"
.OnAction = "PasteAudio"
.FaceId = 23
End With

With objBar.Controls.Add(temporary:=True)
.Caption = "Backup"
.OnAction = "PasteBackup"
.FaceId = 3
End With

End Sub

The three FaceID numbers used here apply the button faces of the New,
Open, and Save built-in toolbar buttons to your custom buttons. To change
the appearance of the button, simply change the number of the FaceID to the
one that represents the image you want to display.

If you want a quick and easy way to see which FaceID numbers correspond
to which button images, John Walkenbach has a couple of excelent solutions
here:

http://j-walk.com/ss/excel/tips/commandbars.htm

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Rob

That is excellent. Works a treat. Thank You.

The buttons are not very inspiring but sufficient to differentiatiate my macros for me. Inevitably, solving this has
raised more interest.

Do the Face ID's have names. Reason I ask is that I found it a little tedious to look through them and it strikes me it
would be useful to list the buttons out on a spreadsheet with the name alongside. Alternatively I could go through and
create it manually but don;t think I can be bothered adding my own narrative
..
Next, there were many blank buttons. Is it possible to create my own buttons and overwrite an existing code.

Finally, and probably the most important. As I was updating my code with your suggestion i.e. ".code = 3" the normal
tips came up e.g. ".Application" , ".BeginGroup" etc but FaceID was not among the suggestions. Any idea why and if so
is there a way to have it incuded in the prompts. I use those prompts to experiment with when I don't know what code
structures are available. It would be great if I could tweak my system to always show every avaolable option.

Thanks again

DavidP
 
Hi David,
Do the Face ID's have names.
Click to expand...

Not in the sense of truly enumerated names that you could substitute for
the numbers. I guess you could say they have names based on the names of the
buttons they came from, but you'll often use FaceIDs for buttons that do
something completely different than the buttons from which the FaceIDs
originated.
Next, there were many blank buttons. Is it possible to create my own
Click to expand...
buttons and overwrite an existing code.

Yes and no. You can create and use your own button icons, but there's no
way to put them "inside Excel" and reference them by number like built-in
FaceIDs. The general method for using a custom button face works like this:
1) Create the button face a copy it as a picture onto a worksheet in your
application (any worksheet will do).
2) When you're building your toolbar, you put the custom button face on it
in the following manner:

Sheet1.Shapes("MyButtonFace").CopyPicture
ctlButton.PasteFace

where "MyButtonFace" is whatever name you've given to the picture and
ctlButton is a reference to the CommandBar Control object that you want to
put the picture on.
Finally, and probably the most important. As I was updating my code with
Click to expand...
your suggestion i.e. ".code = 3" the normal
tips came up e.g. ".Application" , ".BeginGroup" etc but FaceID was not
Click to expand...
among the suggestions. Any idea why and if so
is there a way to have it incuded in the prompts. I use those prompts to
Click to expand...
experiment with when I don't know what code
structures are available. It would be great if I could tweak my system to
Click to expand...
always show every avaolable option.

This was just because of the way the code was structured. The With...End
With blocks you're using only giving you generic CommandBarControl objects.
This type of object can hold a reference to any of the more specific types
of CommandBarControl, but Excel doesn't know at design time what types of
control that is, so the only items displayed on the autocomplete list are
items that apply to all CommandBarControls.

The CommandBarControl type you're adding in your With...End With blocks
is the CommandBarButton object. In order to see the full autocomplete list
for that object you can restructure the code to cache a reference to this
object when you create it, then use the reference to set its properties.
Revisiting the original code again, here's how this would look:

Sub Test()

Dim objBar As Office.CommandBar
Dim ctlButton As Office.CommandBarButton

'Delete CommandBar if it exists
On Error Resume Next
Application.CommandBars("DataEntry").Delete
On Error GoTo 0

Set objBar = Application.CommandBars.Add("DataEntry")

Set ctlButton = objBar.Controls.Add(temporary:=True)
ctlButton.Caption = "Anti Virus"
ctlButton.OnAction = "PasteAntiVirus"
ctlButton.FaceId = 2520

Set ctlButton = objBar.Controls.Add(temporary:=True)
ctlButton.Caption = "Audio"
ctlButton.OnAction = "PasteAudio"
ctlButton.FaceId = 23

Set ctlButton = objBar.Controls.Add(temporary:=True)
ctlButton.Caption = "Backup"
ctlButton.OnAction = "PasteBackup"
ctlButton.FaceId = 3

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


DavidP said:
Rob

That is excellent. Works a treat. Thank You.

The buttons are not very inspiring but sufficient to differentiatiate my
Click to expand...
macros for me. Inevitably, solving this has
raised more interest.

Do the Face ID's have names. Reason I ask is that I found it a little
Click to expand...
tedious to look through them and it strikes me it
would be useful to list the buttons out on a spreadsheet with the name
Click to expand...
alongside. Alternatively I could go through and
create it manually but don;t think I can be bothered adding my own narrative
.
Next, there were many blank buttons. Is it possible to create my own
Click to expand...
buttons and overwrite an existing code.
Finally, and probably the most important. As I was updating my code with
Click to expand...
your suggestion i.e. ".code = 3" the normal
tips came up e.g. ".Application" , ".BeginGroup" etc but FaceID was not
Click to expand...
among the suggestions. Any idea why and if so
is there a way to have it incuded in the prompts. I use those prompts to
Click to expand...
experiment with when I don't know what code
structures are available. It would be great if I could tweak my system to
Click to expand...
always show every avaolable option.
 
Thanks Rob for the very clear answers

David

Hi David,


Not in the sense of truly enumerated names that you could substitute for
the numbers. I guess you could say they have names based on the names of the
buttons they came from, but you'll often use FaceIDs for buttons that do
something completely different than the buttons from which the FaceIDs
originated.

buttons and overwrite an existing code.

Yes and no. You can create and use your own button icons, but there's no
way to put them "inside Excel" and reference them by number like built-in
FaceIDs. The general method for using a custom button face works like this:
1) Create the button face a copy it as a picture onto a worksheet in your
application (any worksheet will do).
2) When you're building your toolbar, you put the custom button face on it
in the following manner:

Sheet1.Shapes("MyButtonFace").CopyPicture
ctlButton.PasteFace

where "MyButtonFace" is whatever name you've given to the picture and
ctlButton is a reference to the CommandBar Control object that you want to
put the picture on.

your suggestion i.e. ".code = 3" the normal
among the suggestions. Any idea why and if so
experiment with when I don't know what code
always show every avaolable option.

This was just because of the way the code was structured. The With...End
With blocks you're using only giving you generic CommandBarControl objects.
This type of object can hold a reference to any of the more specific types
of CommandBarControl, but Excel doesn't know at design time what types of
control that is, so the only items displayed on the autocomplete list are
items that apply to all CommandBarControls.

The CommandBarControl type you're adding in your With...End With blocks
is the CommandBarButton object. In order to see the full autocomplete list
for that object you can restructure the code to cache a reference to this
object when you create it, then use the reference to set its properties.
Revisiting the original code again, here's how this would look:

Sub Test()

Dim objBar As Office.CommandBar
Dim ctlButton As Office.CommandBarButton

'Delete CommandBar if it exists
On Error Resume Next
Application.CommandBars("DataEntry").Delete
On Error GoTo 0

Set objBar = Application.CommandBars.Add("DataEntry")

Set ctlButton = objBar.Controls.Add(temporary:=True)
ctlButton.Caption = "Anti Virus"
ctlButton.OnAction = "PasteAntiVirus"
ctlButton.FaceId = 2520

Set ctlButton = objBar.Controls.Add(temporary:=True)
ctlButton.Caption = "Audio"
ctlButton.OnAction = "PasteAudio"
ctlButton.FaceId = 23

Set ctlButton = objBar.Controls.Add(temporary:=True)
ctlButton.Caption = "Backup"
ctlButton.OnAction = "PasteBackup"
ctlButton.FaceId = 3

End Sub
Click to expand...
 
Back
Top