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

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