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

Menu Bars etc. setting.

Posted by Michele on August 16, 2001 3:22 AM

In my file "DRIVER" I have the following code in "ThisWorksheet", "Open" and it works fine.

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

This DRIVER file, upon the user's request will open other files. When these other files are closed, the Command bars are set back to Visible = True in case the files were opened directly - instead of via this DRIVER - and the user should have his normal settings again. However, if the files were opened via the DRIVER, the DRIVER (sheet 1) is back on the screen but the Command bars are visible and I would like to have them NOT visible but I can't get the above code to execute automatically.

Thanks for your help.
Michele

Posted by Dax on August 16, 2001 4:27 AM

Do you mean you want the commandbars invisible when DRIVER is active and visible when not? If so, put your code which hides and unhides the commandbars in the Workbook_Activate/Workbook_Deactivate event procedures.

HTH,
Dax.

Posted by Michele on August 16, 2001 5:45 AM

Thanks Dax but I have tried this and it doesn't work.
It seems that after closing the other files, the DRIVER workbook is visible/displayed but it hasn't been activated so nothing happens.
It seems what is needed is a "On Get Focus" procedure but that's not available.

Any comments or other possible solutions?

Thanks
Michele

Posted by Dax on August 16, 2001 9:19 AM

Eh? If Excel is the active application and there are say 10 workbooks open (one of them DRIVER) and then 9 of those workbooks are closed then DRIVER will activate. Perhaps I'm not understanding your problem correctly. Can you provide a bit more info?

Posted by Michele on August 17, 2001 12:58 AM

Dax,
It is exactly what I've got - except only 2 or 3 workbooks open; one of which is DRIVER. When the others are closed, I see the DRIVER on the screen but nothing happens. It has the menu bars, etc (which are set by the workbooks that get closed). If I click sheet 2 and then click sheet 1, my code in "Sheet 1", ""Activate_Sheet" procedure gets executed. The code I have here is the same as in "ThisWorkbook"; "Activate_Workbook" procedure, i.e.,
Application.CommandBars(1).Enabled = False
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Chart Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False

At the start "ThisWorkbook"; "Activate_Workbook" I inserted MsgBox ("OK") as a trace and after closing the other workbooks, the MsgBox doesn't get executed. So I see nothing wrong with my code but it is obvious to me that DRIVER popping up on the screen doesn't make it Activate the workbook.

Thanks
Michele

Posted by Michele on August 17, 2001 3:39 AM

The "Other Workbooks" I was refering to set the Menu Bars, Formula Bar, Headings to TRUE before closing. But then I would expect DRIVER to be Activated and set these to FALSE upon DRIVER being displayed. But as I said, this is not happening.

However, if the "Other Workbooks" are plain - no macros, no VBA code - when they are closed, the DRIVER is on display and in this case is Active and my code gets executed. Unfortunately it is not problem free - now and then I get a run time error: Method 'Display Formula bar' of object '_Application' failed.
My code is:
Application.DisplayFormulaBar = False
Regards,
Michele