removing and replacing toolbars

[Paul James]

I've got an application in which I've removed most of the normal Excel
screen objects like row and column headers, worksheet tabs and scroll bars,
because I give the users command buttons and hyperlinks to navigate around
the app. I would also like to remove all toolbars, but then have those same
toolbars reappear whenever the user opens any other workbook after my
application has closed. Is there a way to do this with VBA?

Thanks in advance,

Paul

 

[Bill Manville]

I would also like to remove all toolbars, but then have those same
toolbars reappear whenever the user opens any other workbook after my
application has closed. Is there a way to do this with VBA?

Close.
You would replace the toolbars as your application is closing.

Sub Auto_Open()
ShowToolbars False
End Sub

Sub Auto_Close()
ShowToolbars True
End Sub

Sub ShowToolbars(ToBeSeen As Boolean)
Dim CB As CommandBar
For Each CB In Application.CommandBars
CB.Enabled = ToBeSeen
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Paul James]

Works great. Thanks (again) for the code, Bill.

I'm wondering about something - if the power goes out or there is a system
crash while the application is running, so the Auto_Close event doesn't have
a chance to restore the toolbars (which include the menu bar), how does the
user restore the objects the next time Excel runs?

Paul

 

[Bill Manville]

if the power goes out or there is a system
crash while the application is running, so the Auto_Close event doesn't have
a chance to restore the toolbars (which include the menu bar), how does the
user restore the objects the next time Excel runs?

Been there - just double-click on your workbook and then close Excel using
wither a button in your application or the x button on the title bar. Your
Auto_Close will put the commandbars back.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Paul James]

Good solution. Thanks, Bill.

Have a nice weekend.

 

[Big Chris]

I found this code in a search and it's almost exactly what I want. An
yes, it works great. However, I want to maintain the Worksheet Men
Bar.....the one with File, Edit, View etc, but not the actua
toolbars.

Would anyone have any idea how I could adjust this code to enable me t
achieve this objective?

Thanks to Bill for his original answer which almost does the trick fo
me too, and thanks in advance for looking at my problem.

Regards