MrExcel.com - Your One Stop for Excel Tips & Solutions

Hide - Unhide - Hide Bars

Posted by Michele on June 28, 2001 5:10 AM

Upon opening, my Cat.xls file hides various bars.
From this file, the user can select a file from a list and ask to open that file. Cat.xls must first unhide the bars and then open the requested file.

The requested file may or may not hide some of these bars (depending on which file gets oppened) and when this file gets closed any hiden bars are are unhide. The Cat.xls is then automatically on display but I can't get the bars to automatically hide again. I have tried to place the hide instruction in the "Activate", in the "WindowActivate", in the "SheetActivate" of "ThisWorkbook" as well as in the "GotFocus" of Sheet 1 but nothing works.

The instructions I use are:
Application.CommandBars(1).Enabled = *
Application.CommandBars("Standard").Visible = *
Application.CommandBars("Formatting").Visible = *
Application.DisplayFormulaBar = *
ActiveWindow.DisplayHeadings = *
* = True or False for Unhide and Hide

Thanks for the help!
Michele

Posted by Damon Ostrander on June 28, 2001 9:03 AM

Hi Michele,

I suggest you use the WorkbookActivate event, which is one of the application object events available in Excel. These events are at a level above the workbook object events, and therefore can operate across workbook activations, etc. There are some examples of how to do application object events in the Excel helps.

Happy computing.

Damon

Posted by Michele on June 28, 2001 9:33 AM

Hello Damon,
Thank you very much for time & your reply.
However, I'm not sure I know what you mean.
When I go to the Object box in the Code Display, I only have General and Worksheet.
How do I get to this WorkbookActivate event you mention?
Thanks
Michele

Posted by Damon Ostrander on June 28, 2001 2:10 PM

Michele,

I should have mentioned that the helps for application object events is in the VBA helps, not the Excel helps. You will have to go into the VBE and request help on "Application Object Events." In the explanation there you will find out that you must create a Class Module to handle this event, but this is not difficult and Microsoft provides an example.

Damon


Posted by Michele on June 29, 2001 6:02 AM

Damon,

Thanks again but it doesn't work.

First, I cannot find any Application Object Events" or any semblance of this in the VGE Help.

Nevertheless, I created a Class module as follows but it doesn't get executed niether when I open the file nor later when the window becomes displayed (after closing another file).

My class module is:
Public Sub Class_Initialize()
Application.CommandBars(1).Enabled = False
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
Beep
End Sub

It seems all that can be created as Class module that would be for automatic exection is Class_Initialize() or Class_Terminate().

I hate to bother you again but if you can help, I certainly would appreciate it.

Regards,
Michele