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)

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

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 *


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

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
he

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 *


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

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

On Mon, 17 Nov 2003 00:29:44 -0800, "Rob Bovey" wrote:

.FaceId = 3

Thanks Rob for the very clear answers

David

On Mon, 17 Nov 2003 10:57:38 -0800, "Rob Bovey" wrote:

Hi David,

Do the Face ID's have names.

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
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
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.

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

There is a list of face IDs on the Microsoft Knowledge Base (no 159466)
at the following URL:

http://support.microsoft.com/default.aspx?kbid=159466

I'm not sure if it gives absolutely everything, but it is certainly a
long list.

Graham

Estates Services
University of Sunderland
England


DavidP wrote:

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

On Mon, 17 Nov 2003 00:29:44 -0800, "Rob Bovey" wrote:



.FaceId = 3