I have a button which creates, enables, and makes visible a toolbar. How do
I delete the toolbar when the user is done with it? I can make a toolbar
button that deletes the toolbar, but how do I do this if the user uses the
view menu or clicks the X in the corner of the toolbar to make it not
visible? So, if the toolbar exists, I would like it to be visible, if it is
not visible or made not visible, I don't want it to continue to exist. Any
ideas?

Thanks
Dave

Sorry, seem to have figured it out:

set cmdbar = application.commandbars.add("MyBar")
with cmdbar
.protection = msoBarNoChangeVisible + msoBarNoCustomize
End with

Thanks again
Dave


"drhalter" wrote:

I have a button which creates, enables, and makes visible a toolbar. How do
I delete the toolbar when the user is done with it? I can make a toolbar
button that deletes the toolbar, but how do I do this if the user uses the
view menu or clicks the X in the corner of the toolbar to make it not
visible? So, if the toolbar exists, I would like it to be visible, if it is
not visible or made not visible, I don't want it to continue to exist. Any
ideas?

Thanks
Dave

Hi Drhalter,

So, if the toolbar exists, I would like it to be visible, if it is
not visible or made not visible, I don't want it to continue to exist. Any
ideas?



This gives you an idea how to create a bar that cannot be closed:

Sub createbar()
Dim oBar As CommandBar
removebar
Set oBar = CommandBars.Add
oBar.Name = "Test"
oBar.Protection = msoBarNoChangeVisible
oBar.Visible = True
End Sub

Sub removebar()
On Error Resume Next
Application.CommandBars("Test").Delete
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

That's great. I posted just prior to yours, I happened to think of how to do
it about 2 seconds after I posted the original question. But, any ideas on
how to test whether the toolbar exists? I have the following code in a
workbook_sheetcalculate event:

If application.commandbars("myBar").visible = true then

but I get an error when myBar doesn't exist. The code works great when the
toolbar does exist.

Thanks
Dave

"Jan Karel Pieterse" wrote:

Hi Drhalter,

So, if the toolbar exists, I would like it to be visible, if it is
not visible or made not visible, I don't want it to continue to exist. Any
ideas?



This gives you an idea how to create a bar that cannot be closed:

Sub createbar()
Dim oBar As CommandBar
removebar
Set oBar = CommandBars.Add
oBar.Name = "Test"
oBar.Protection = msoBarNoChangeVisible
oBar.Visible = True
End Sub

Sub removebar()
On Error Resume Next
Application.CommandBars("Test").Delete
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Drhalter,

That's great. I posted just prior to yours, I happened to think of how to do
it about 2 seconds after I posted the original question. But, any ideas on
how to test whether the toolbar exists? I have the following code in a
workbook_sheetcalculate event:

If application.commandbars("myBar").visible = true then

but I get an error when myBar doesn't exist. The code works great when the
toolbar does exist.


Dim oBar as CommandBar
On Error Resume next
Set oBar=Application.Commandbars("YourBar")
If oBar is nothing then
'Bar is absent
Else
'Do your thing
End If

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Thanks, that does it.

"Jan Karel Pieterse" wrote:

Hi Drhalter,

That's great. I posted just prior to yours, I happened to think of how to do
it about 2 seconds after I posted the original question. But, any ideas on
how to test whether the toolbar exists? I have the following code in a
workbook_sheetcalculate event:

If application.commandbars("myBar").visible = true then

but I get an error when myBar doesn't exist. The code works great when the
toolbar does exist.


Dim oBar as CommandBar
On Error Resume next
Set oBar=Application.Commandbars("YourBar")
If oBar is nothing then
'Bar is absent
Else
'Do your thing
End If

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com