Excel Hiding and Showing toolbars :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Hiding and Showing toolbars
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

EconSean
Board Regular


Joined: 22 Apr 2002
Posts: 84


Status: Offline

 Reply with quote  

Forgive me if this has been covered, but I couldn't find it referenced anywhere, and the problem is making me a little crazy.

Ok, I want to hide toolbars during the Auto_Open sub, which is easy enough to do. I want to put them back during the Auto_Close sub, which again, is easy enough to do.

My problem is this: suppose the user has a toolbar already hidden (this is how he or she wants Excel set up) before my program runs. Now, if I try to hide something that is already hidden (via my Auto_Open sub), there is no problem, the VBA routine chugs along happily. The trouble comes at the Auto_Close sub; I may unhide something that should be left hidden (at least as far as this particular user is concerned).

Thus, I want to leave Excel set up exactly as it was before the user started my program.

Any thoughts on how to best accomplish this? Please let me know if I have done a poor job describing my dilemma and I will try to explain differently.

Thanks in advance for your suggestions.

Regards,

Sean C.

Post Tue May 28, 2002 5:46 pm 
 View user's profile Send private message

dk
MrExcel MVP


Joined: 16 Feb 2002
Posts: 1836
Location: Sydney, Australia
Flag: England

Status: Offline

 Reply with quote  

Hello Sean,

What you can do is run a macro which stores all of the users visible toolbars before you hide them. E.g. this code will list all of the visible toolbars on a sheet called Toolbars (which you will probably want to keep hidden):-

code:

Sub StoreToolbars()
Dim comBar As CommandBar, lngRow As Long

lngRow = 1
Sheets("Toolbars").Range("A1:A65536").ClearContents
For Each comBar In Excel.CommandBars
    If comBar.Visible = True Then
        Sheets("Toolbars").Cells(lngRow, 1) = comBar.Name
        lngRow = lngRow + 1
    End If
Next comBar

End Sub




Now, once this code has run you can hide all of the toolbars you want to hide. Once you want to restore the previous settings use something like this:-

code:

Sub UnhideBars()
Dim lngRow As Long
Dim strToolbarName As String
For lngRow = 1 To Sheets("Toolbars").UsedRange.Rows.Count
    strToolbarName = Sheets("Toolbars").Cells(lngRow, 1)
    Excel.CommandBars(strToolbarName).Visible = True
Next lngRow
End Sub




HTH,

Dan

Post Tue May 28, 2002 7:13 pm 
 View user's profile Send private message Visit poster's website

EconSean
Board Regular


Joined: 22 Apr 2002
Posts: 84


Status: Offline

 Reply with quote  

Thanks Dan, that was very handy.

Regards,

Sean

Post Wed May 29, 2002 9:43 am 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.