(Solved) Excel - Macros to Remove and Restore Toolbars

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Wildheart

Thread Starter
Joined
Oct 12, 2003
Messages
4
First of all, great site, wish I would have found it years ago. I often create Excel spreadsheets (often elaborate) that others use and I like to make them as ummm clean and idiot proof as possible ;). I would like to create an autoexecute macro that when a file is opened, it will remove all toolbars and menus (spreadsheet functions and navigation will be done with macro buttons). I would also like to have a save and close macro button that will restore default menus and toolbars etc. Any help with the auteoexecute & exit macro would be appreciated.

Thanks
Wildheart
 
Joined
Aug 30, 2003
Messages
2,702
>> autoexecute macro that when a file is opened

Private Sub WorkBook_Open()
(do stuff)
End Sub

This goes in the workbook module -- to access it, rightclick the XL icon left of File in the menu bar. There may be issues with what you class as the "default ... toolbars", however you may find some of these bits and pieces useful.

Private Sub Workbook_Open()
Application.CommandBars.ActiveMenuBar.Enabled = False
For Each Bar In Application.CommandBars
On Error Resume Next
Bar.Visible = False
Next
Application.DisplayFormulaBar = False
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Toolbars(1).Visible = True
Application.Toolbars(2).Visible = True
Application.CommandBars.ActiveMenuBar.Enabled = True
ActiveWorkbook.Close (SaveChanges = True)
End Sub

You can get a handle on most stuff by recording it while doing it, then studying the code afterwards.

Rgds,
Andy
 
Joined
May 4, 2002
Messages
743
...and I lost my menu bar and formula bar - seemingly for good - playing around with your code, cat daddy! :eek:
I still am not exactly sure why.

I went back and inserted the "vis" code and got it back. But I was a wee way nervous for a moment! :(



- Castleheart
 
Joined
May 4, 2002
Messages
743
No need for what?
Are you trying to confound me?
Don't mess with my mind - there's not enough to go around.




Well then... how about opening a blank spread sheet and inserting the code:


Private Sub Workbook_Open()
Application.CommandBars.ActiveMenuBar.Enabled = False
For Each Bar In Application.CommandBars
On Error Resume Next
Bar.Visible = False
Next
Application.DisplayFormulaBar = False
End Sub


Then save and close
Then open it


What then is your solution to restoring tool and menu bars back to EXCEL after that exercise?

Shine on me!


- C




I know the answer.
Andy, be quiet!
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
Well...there's 2 things if you did not know how you lost them, AND you did not want to use VBA to get them back.

One is to find and rename or delete the *.xlb file.

Two is to rename the Excel reg key. See Excel troubleshooting at www.theofficeexperts.com/excel.htm

:D
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top