Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
Hi folks.
I have code in my auto_open macro to hide all the excel toolbars and menu bars to give a clean dictator program appearance. I also have included in the auto_close macro the code to re-show these menu bars. All works fine generally....however... ......some people have been having problems with Excel 2003 if the program is not shut-down correctly. When they start up Excel (Not my workbook program) again, the menu bars are still hidden. I believed that executing my program, and then exiting it again correctly would fix this, because the auto_close macro would have the opportunity to show the menus again, but I've been told it doesn't! I created a basic workbook file with two buttons, one to show and one to hide the menus. Using this seemingly works fine to retrieve the menu bars. I've been having problems replicating this issue with Excel 2002. I've run my program and ended Excel with the Windows Task Manager, and I've run it and shut down my system cold with the on/off button, but the toolbars are always there when I start back up. Is there something about Excel 2003 that I'm missing? Why does the auto_close macro not retrieve the hidden menu bar and toolbars when the text of the auto_close code is the exact same as that within the show/hide workbook? Any advice is much appreciated. Thanks. The code segments are as follows: Sub Auto_open() If Val(Application.Version) < 9 Then MsgBox " Program Requires Microsoft Excel 2002 or Newer ", vbOKOnly + vbExclamation, "ERROR" ActiveWorkbook.Save Application.Quit End If If Val(Application.Version) = 9 Then Response = MsgBox("Program Has Not Been Tested Below Microsoft Excel 2002 - Start Anyway? ", vbYesNo + vbQuestion, "ERROR") End If If Response = vbNo Then ActiveWorkbook.Save Application.Quit End If Application.WindowState = xlMaximized Application.ShowWindowsInTaskbar = False Application.CommandBars("Worksheet Menu Bar").Enabled = False Application.IgnoreRemoteRequests = True Application.EnableCancelKey = xlDisable With ActiveWindow Application.Caption = "My Program" .DisplayGridlines = False .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False .Zoom = 100 End With With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With Application.AutoRecover.Enabled = False ActiveWorkbook.EnableAutoRecover = False Application.CommandBars("Standard").Visible = False Application.CommandBars("Formatting").Visible = False Application.CommandBars("Forms").Visible = False Application.CommandBars("Borders").Visible = False Application.CommandBars("Chart").Visible = False Application.CommandBars("Control Toolbox").Visible = False Application.CommandBars("Drawing").Visible = False Application.CommandBars("Exit Design Mode").Visible = False Application.CommandBars("External Data").Visible = False Application.CommandBars("Formula Auditing").Visible = False Application.CommandBars("Picture").Visible = False Application.CommandBars("PivotTable").Visible = False Application.CommandBars("Protection").Visible = False Application.CommandBars("Reviewing").Visible = False Application.CommandBars("Text To Speech").Visible = False Application.CommandBars("Visual Basic").Visible = False Application.CommandBars("Watch Window").Visible = False Application.CommandBars("Web").Visible = False Application.CommandBars("WordArt").Visible = False End Sub Sub Auto_close() ' ' Auto_close Macro ' Macro recorded 10/12/2007 ' ' Workbooks("MyProgram").Activate ActiveWorkbook.Save Application.ShowWindowsInTaskbar = True Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.IgnoreRemoteRequests = False Application.EnableCancelKey = xlInterrupt Sheets("Sheet1").Select With ActiveWindow .DisplayGridlines = True .DisplayHeadings = True .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayWorkbookTabs = True End With With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With Application.AutoRecover.Enabled = True ActiveWorkbook.EnableAutoRecover = True Application.CommandBars("Standard").Visible = True Application.CommandBars("Formatting").Visible = True Application.Caption = Empty Application.WindowState = xlNormal Application.Quit End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
By hiding the menu bar when opening your workbook you are also preventing users from doing work in other workbooks they may have open. If you insist on running a dictator app, then my recommendation is to have / tell users (to) open the workbook in a new instance of Excel. Any issues would then be limited to the new instance and would go away when the new instance was closed. Note also that the Auto_Open and Auto_Close subs do not run when a workbook is opened via code. Only when the workbook is opened manually. (the RunAutoMacros method can be used to force them to run) -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "dim" wrote in message Hi folks. I have code in my auto_open macro to hide all the excel toolbars and menu bars to give a clean dictator program appearance. I also have included in the auto_close macro the code to re-show these menu bars. All works fine generally....however... ......some people have been having problems with Excel 2003 if the program is not shut-down correctly. When they start up Excel (Not my workbook program) again, the menu bars are still hidden. I believed that executing my program, and then exiting it again correctly would fix this, because the auto_close macro would have the opportunity to show the menus again, but I've been told it doesn't! I created a basic workbook file with two buttons, one to show and one to hide the menus. Using this seemingly works fine to retrieve the menu bars. I've been having problems replicating this issue with Excel 2002. I've run my program and ended Excel with the Windows Task Manager, and I've run it and shut down my system cold with the on/off button, but the toolbars are always there when I start back up. Is there something about Excel 2003 that I'm missing? Why does the auto_close macro not retrieve the hidden menu bar and toolbars when the text of the auto_close code is the exact same as that within the show/hide workbook? Any advice is much appreciated. Thanks. The code segments are as follows: -snip- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
Hi Jim, thanks for the reply.
This is the main user interface workbook, and any relevant code within it executes using the RunAutoMacros command. My problem is if people run this workbook when they have no other workbook open. Then if it goes pear shaped, this is the only instance of Excel. I can't really tell people that they have to open Excel, minimise the window, then open Excel again and run my workbook, its not practical. :-( Do you have any idea why re-opening and then closing my workbook doesn't reset the toolbars in some cases? Thanks. "Jim Cone" wrote: |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
I believe when Excel is not shut down normally the auto_close sub does not run.
The usual place to put code that you want to run when a workbook opens/closes is the ThisWorkbook...Workbook_Open and Workbook_Close event subs. You had better give those a try. By the way those are also not infallible. You probably ought to put the menu bar enabled = true code in both.the Auto_Close and Workbook_Close subs. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "dim" wrote in message Hi Jim, thanks for the reply. This is the main user interface workbook, and any relevant code within it executes using the RunAutoMacros command. My problem is if people run this workbook when they have no other workbook open. Then if it goes pear shaped, this is the only instance of Excel. I can't really tell people that they have to open Excel, minimise the window, then open Excel again and run my workbook, its not practical. :-( Do you have any idea why re-opening and then closing my workbook doesn't reset the toolbars in some cases? Thanks. "Jim Cone" wrote: |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
I developed a similar approach to you in an early application & quickly found
myself to be very unpopular. Users genearlly do not like having functionality taken away & I found they just refused to use it. I can't add much more than Jim has already said but I do note that you are hiding all the toolbars but not returning them to the state the user has set on their desktop which they equally find just as annoying. Have a play with following code and see if useful for your application. It stores all visible commandbars (not worksheet menu bar) which will be restored when you exit your application. 'place this code in 'Workbook_BeforeClose event HideBars (xlOff) 'place this code in 'Workbook_Open event HideBars (xlOn) 'normal code module Sub HideBars(state) Static myoldbars As New Collection Dim mybar If state = xlOn Then For Each mybar In Application.CommandBars If mybar.Type < 1 And mybar.Visible Then myoldbars.Add mybar mybar.Visible = False End If Next mybar Else 'restore bars For Each mybar In myoldbars mybar.Visible = True Next End If End Sub Hope useful -- jb "dim" wrote: Hi folks. I have code in my auto_open macro to hide all the excel toolbars and menu bars to give a clean dictator program appearance. I also have included in the auto_close macro the code to re-show these menu bars. All works fine generally....however... .....some people have been having problems with Excel 2003 if the program is not shut-down correctly. When they start up Excel (Not my workbook program) again, the menu bars are still hidden. I believed that executing my program, and then exiting it again correctly would fix this, because the auto_close macro would have the opportunity to show the menus again, but I've been told it doesn't! I created a basic workbook file with two buttons, one to show and one to hide the menus. Using this seemingly works fine to retrieve the menu bars. I've been having problems replicating this issue with Excel 2002. I've run my program and ended Excel with the Windows Task Manager, and I've run it and shut down my system cold with the on/off button, but the toolbars are always there when I start back up. Is there something about Excel 2003 that I'm missing? Why does the auto_close macro not retrieve the hidden menu bar and toolbars when the text of the auto_close code is the exact same as that within the show/hide workbook? Any advice is much appreciated. Thanks. The code segments are as follows: Sub Auto_open() If Val(Application.Version) < 9 Then MsgBox " Program Requires Microsoft Excel 2002 or Newer ", vbOKOnly + vbExclamation, "ERROR" ActiveWorkbook.Save Application.Quit End If If Val(Application.Version) = 9 Then Response = MsgBox("Program Has Not Been Tested Below Microsoft Excel 2002 - Start Anyway? ", vbYesNo + vbQuestion, "ERROR") End If If Response = vbNo Then ActiveWorkbook.Save Application.Quit End If Application.WindowState = xlMaximized Application.ShowWindowsInTaskbar = False Application.CommandBars("Worksheet Menu Bar").Enabled = False Application.IgnoreRemoteRequests = True Application.EnableCancelKey = xlDisable With ActiveWindow Application.Caption = "My Program" .DisplayGridlines = False .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False .Zoom = 100 End With With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With Application.AutoRecover.Enabled = False ActiveWorkbook.EnableAutoRecover = False Application.CommandBars("Standard").Visible = False Application.CommandBars("Formatting").Visible = False Application.CommandBars("Forms").Visible = False Application.CommandBars("Borders").Visible = False Application.CommandBars("Chart").Visible = False Application.CommandBars("Control Toolbox").Visible = False Application.CommandBars("Drawing").Visible = False Application.CommandBars("Exit Design Mode").Visible = False Application.CommandBars("External Data").Visible = False Application.CommandBars("Formula Auditing").Visible = False Application.CommandBars("Picture").Visible = False Application.CommandBars("PivotTable").Visible = False Application.CommandBars("Protection").Visible = False Application.CommandBars("Reviewing").Visible = False Application.CommandBars("Text To Speech").Visible = False Application.CommandBars("Visual Basic").Visible = False Application.CommandBars("Watch Window").Visible = False Application.CommandBars("Web").Visible = False Application.CommandBars("WordArt").Visible = False End Sub Sub Auto_close() ' ' Auto_close Macro ' Macro recorded 10/12/2007 ' ' Workbooks("MyProgram").Activate ActiveWorkbook.Save Application.ShowWindowsInTaskbar = True Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.IgnoreRemoteRequests = False Application.EnableCancelKey = xlInterrupt Sheets("Sheet1").Select With ActiveWindow .DisplayGridlines = True .DisplayHeadings = True .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayWorkbookTabs = True End With With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With Application.AutoRecover.Enabled = True ActiveWorkbook.EnableAutoRecover = True Application.CommandBars("Standard").Visible = True Application.CommandBars("Formatting").Visible = True Application.Caption = Empty Application.WindowState = xlNormal Application.Quit End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
Thanks Jim and John,
John I tried that code, but I'm getting told that - "Compile Error: Procedure declaration does not match event or procedure having the same name" Any ideas? Here's the code in ThisWorkbook: Private Sub Workbook_BeforeClose() HideBars (xlOff) End Sub Private Sub Workbook_Open() HideBars (xlOn) End Sub And in Module1: Sub HideBars(state) Static myoldbars As New Collection Dim mybar If state = xlOn Then For Each mybar In Application.CommandBars If mybar.Type < 1 And mybar.Visible Then myoldbars.Add mybar mybar.Visible = False End If Next mybar Else For Each mybar In myoldbars mybar.Visible = True Next End If End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
The BeforeClose declaration is incorrect; it should be...
Private Sub Workbook_BeforeClose(Cancel As Boolean) You should almost never type event header declarations yourself... simply pick them from the right-most drop down list in the code window (that way, they will always be guaranteed correct). Rick "dim" wrote in message ... Thanks Jim and John, John I tried that code, but I'm getting told that - "Compile Error: Procedure declaration does not match event or procedure having the same name" Any ideas? Here's the code in ThisWorkbook: Private Sub Workbook_BeforeClose() HideBars (xlOff) End Sub Private Sub Workbook_Open() HideBars (xlOn) End Sub And in Module1: Sub HideBars(state) Static myoldbars As New Collection Dim mybar If state = xlOn Then For Each mybar In Application.CommandBars If mybar.Type < 1 And mybar.Visible Then myoldbars.Add mybar mybar.Visible = False End If Next mybar Else For Each mybar In myoldbars mybar.Visible = True Next End If End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
Thanks Rick,
I changed it to: Private Sub Workbook_BeforeClose(Cancel As Boolean) HideBars (xlOff) End Sub Private Sub Workbook_Open(Cancel As Boolean) HideBars (xlOn) End Sub But I'm still getting the same error....any ideas? I don't use the drop down because it never seems to work right, its probably the way I'm using it. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
Cancel my last message! Its working now. Except it doesn't turn off the Main
Menu bar, and those which are turned off, don't seem to come back on when it closes. I've started it...the menus hide....exit it....start Excel as normal....the menus are still gone? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAJOR PROBLEM! --- Menu Bars don't unhide?
I made up my own recording of the menu bars using IF statements and put it
into the Workbook Close event. Its below if anyone is interested. It works fine so I'm going to expand it to include gridlines and such. Can someone tell me whether the Workbook_BeforeClose event or the Auto_Close procedure executes first? Thanks. ---------------------------------------------------------------------------------------- Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False Sheets("Bars").Select Application.CommandBars("Worksheet Menu Bar").Enabled = True If Range("B20").Value = 1 Then Application.DisplayFormulaBar = True End If If Range("B21").Value = 1 Then Application.DisplayStatusBar = True End If If Range("B1").Value = 1 Then Application.CommandBars("Standard").Visible = True End If If Range("B2").Value = 1 Then Application.CommandBars("Formatting").Visible = True End If If Range("B3").Value = 1 Then Application.CommandBars("Forms").Visible = True End If If Range("B4").Value = 1 Then Application.CommandBars("Borders").Visible = True End If If Range("B5").Value = 1 Then Application.CommandBars("Chart").Visible = True End If If Range("B6").Value = 1 Then Application.CommandBars("Control Toolbox").Visible = True End If If Range("B7").Value = 1 Then Application.CommandBars("Drawing").Visible = True End If If Range("B8").Value = 1 Then Application.CommandBars("Exit Design Mode").Visible = True End If If Range("B9").Value = 1 Then Application.CommandBars("External Data").Visible = True End If If Range("B10").Value = 1 Then Application.CommandBars("Formula Auditing").Visible = True End If If Range("B11").Value = 1 Then Application.CommandBars("Picture").Visible = True End If If Range("B12").Value = 1 Then Application.CommandBars("PivotTable").Visible = True End If If Range("B13").Value = 1 Then Application.CommandBars("Protection").Visible = True End If If Range("B14").Value = 1 Then Application.CommandBars("Reviewing").Visible = True End If If Range("B15").Value = 1 Then Application.CommandBars("Text To Speech").Visible = True End If If Range("B16").Value = 1 Then Application.CommandBars("Visual Basic").Visible = True End If If Range("B17").Value = 1 Then Application.CommandBars("Watch Window").Visible = True End If If Range("B18").Value = 1 Then Application.CommandBars("Web").Visible = True End If If Range("B19").Value = 1 Then Application.CommandBars("WordArt").Visible = True End If Sheets("Sheet1").Select Application.ScreenUpdating = True End Sub Private Sub Workbook_Open() Application.ScreenUpdating = False Sheets("Bars").Select Range("A1:A21").ClearContents Application.CommandBars("Worksheet Menu Bar").Enabled = False If Application.DisplayFormulaBar = True Then Range("B20").Value = 1 Application.DisplayFormulaBar = False End If If Application.DisplayStatusBar = True Then Range("B21").Value = 1 End If If Application.CommandBars("Standard").Visible = True Then Range("B1").Value = 1 Application.CommandBars("Standard").Visible = False End If If Application.CommandBars("Formatting").Visible = True Then Range("B2").Value = 1 Application.CommandBars("Formatting").Visible = False End If If Application.CommandBars("Forms").Visible = True Then Range("B3").Value = 1 Application.CommandBars("Forms").Visible = False End If If Application.CommandBars("Borders").Visible = True Then Range("B4").Value = 1 Application.CommandBars("Borders").Visible = False End If If Application.CommandBars("Chart").Visible = True Then Range("B5").Value = 1 Application.CommandBars("Chart").Visible = False End If If Application.CommandBars("Control Toolbox").Visible = True Then Range("B6").Value = 1 Application.CommandBars("Control Toolbox").Visible = False End If If Application.CommandBars("Drawing").Visible = True Then Range("B7").Value = 1 Application.CommandBars("Drawing").Visible = False End If If Application.CommandBars("Exit Design Mode").Visible = True Then Range("B8").Value = 1 Application.CommandBars("Exit Design Mode").Visible = False End If If Application.CommandBars("External Data").Visible = True Then Range("B9").Value = 1 Application.CommandBars("External Data").Visible = False End If If Application.CommandBars("Formula Auditing").Visible = True Then Range("B10").Value = 1 Application.CommandBars("Formula Auditing").Visible = False End If If Application.CommandBars("Picture").Visible = True Then Range("B11").Value = 1 Application.CommandBars("Picture").Visible = False End If If Application.CommandBars("PivotTable").Visible = True Then Range("B12").Value = 1 Application.CommandBars("PivotTable").Visible = False End If If Application.CommandBars("Protection").Visible = True Then Range("B13").Value = 1 Application.CommandBars("Protection").Visible = False End If If Application.CommandBars("Reviewing").Visible = True Then Range("B14").Value = 1 Application.CommandBars("Reviewing").Visible = False End If If Application.CommandBars("Text To Speech").Visible = True Then Range("B15").Value = 1 Application.CommandBars("Text To Speech").Visible = False End If If Application.CommandBars("Visual Basic").Visible = True Then Range("B16").Value = 1 Application.CommandBars("Visual Basic").Visible = False End If If Application.CommandBars("Watch Window").Visible = True Then Range("B17").Value = 1 Application.CommandBars("Watch Window").Visible = False End If If Application.CommandBars("Web").Visible = True Then Range("B18").Value = 1 Application.CommandBars("Web").Visible = False End If If Application.CommandBars("WordArt").Visible = True Then Range("B19").Value = 1 Application.CommandBars("WordArt").Visible = False End If Sheets("Sheet1").Select Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with Menu Bars / Toolbars problem | Excel Programming | |||
Lost all my menu bars and tool bars | Excel Discussion (Misc queries) | |||
Major linking problem | Excel Programming | |||
New Menu on Worksheet & Chart Menu Bars | Excel Programming | |||
linking chart menu bars and worksheet menu bars | Excel Programming |