MAJOR PROBLEM! --- Menu Bars don't unhide?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dim - 29 Apr 2008 18:29 GMT

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

Reply to this Message

Jim Cone - 29 Apr 2008 19:00 GMT

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)

Signature

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-

Reply to this Message

dim - 29 Apr 2008 19:31 GMT

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.

Reply to this Message

Jim Cone - 29 Apr 2008 20:02 GMT

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.

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"dim" <dim@discussions.microsoft.com>
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:

Reply to this Message

john - 29 Apr 2008 20:35 GMT

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

Signature

> Hi folks.
>
[quoted text clipped - 121 lines]
>
> End Sub

Reply to this Message

dim - 30 Apr 2008 11:29 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 30 Apr 2008 18:35 GMT

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

> Thanks Jim and John,
>
[quoted text clipped - 36 lines]
> End If
> End Sub

Reply to this Message

dim - 30 Apr 2008 21:09 GMT

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.

Reply to this Message

dim - 30 Apr 2008 21:18 GMT

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?

Reply to this Message

dim - 30 Apr 2008 22:18 GMT

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 to this Message