1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

(Solved) Excel - Macros to Remove and Restore Toolbars

Discussion in 'Business Applications' started by Wildheart, Oct 12, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Wildheart

    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
     
  2. XL Guru

    XL Guru

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

    Wildheart Thread Starter

    Joined:
    Oct 12, 2003
    Messages:
    4
    Thanks Andy, it works perfect.
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Nice, Andy. I changed the title of the post. :)
     
  5. CastleHeart

    CastleHeart

    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
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    No need, CH.
    I know how to get them back too. :)
     
  7. CastleHeart

    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!
     
  8. Anne Troy

    Anne Troy

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

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    :eek:

    Well, alrighty then!


    ;)











    If it wasn't for the future,
    there'd be no tomorrow!
     
  10. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/171414

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice