Problem with menu visibility 1

[LW]

I have an app where I use forms for user input and store that input on sheets that the user never sees. I use the following code to hide all of the menus:

Public Sub EverythingOff()
Application.ScreenUpdating = False
Dim cb As CommandBar
For Each cb In CommandBars
Application.ScreenUpdating = False
Debug.Print cb.Name
Debug.Print cb.Visible
If cb.Type < 3 Then cb.Enabled = False
Next cb
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
With Application
.DisplayFormulaBar = False
.DisplayFullScreen = True

.DisplayScrollBars = False
.DisplayStatusBar = False
End With
Set cb = Nothing
End Sub

and this code in Workbook_BeforeClose:

Public Sub EverythingOn()
Dim cb As CommandBar

For Each cb In CommandBars
Debug.Print cb.Name
Debug.Print cb.Visible

If cb.Type < 3 Then cb.Enabled = True
If cb.Name = &quot;IWD&quot; Then
cb.Enabled = False
End If
Next cb
With ActiveWindow
.DisplayHeadings = True
.DisplayWorkbookTabs = True
End With
With Application
.DisplayFullScreen = False
.DisplayFormulaBar = True
.DisplayScrollBars = True
.DisplayStatusBar = True
End With
'Application.CommandBars.ActiveMenuBar.Enabled = False
Application.ScreenUpdating = True
Set cb = Nothing
End Sub

When I select Cancel from the Save dialog, the Workbook Menu bar and the other bars that were visible before are not showing. From the intermediate window they are enabled and the Visibility is true. I hav a feeling they are being covered up by somthing but I cannot figure out what for sure.
Any Ideas?

 

[Pcfred]

something I stumbed upon...

' The following routine when run the first time will store all of the
' toolbars visible property then hide all of the visible toolbars. When
' the routine is run a second time, it will restore the toolbars to
' their original state the first time the code was executed.

' This Sub procedure will toggle all currently visible toolbars to
' hidden and when rerun will restore the toolbars.

Sub ToggleToolbars()
' Creates a 20 element array to keep track of current toolbar
' settings on the first iteration through the routine.
' This limits this routine to 20 toolbars total (increasing this
' number will allow for more custom toolbars).
' In Microsoft Excel 97 or Microsoft Excel 98, it is recommended
' that you use a value of at least 80.

Static CurrentToolSet(20) As Boolean
Static Flag As Boolean
Dim i As Integer

' If this is the first time through the routine, do this...
If Flag = False Then

' Loop through all of the toolbars.
For i = 1 To Application.Toolbars.Count

' Store the visible property of each toolbar in the array
' CurrentToolSet.
CurrentToolSet(i) = Application.Toolbars(i).Visible
' Hide all of the toolbars.
Application.Toolbars(i).Visible = False

' End of loop.
Next i

' Set flag to true to skip this section next time through.
Flag = True
Else

' Loop through all of the toolbars.
For i = 1 To Application.Toolbars.Count

' Restore toolbar setting to the original value as saved in
' the array.
Application.Toolbars(i).Visible = CurrentToolSet(i)

' End of loop.
Next i

' Set flag back to false to hide visible toolbars on the next
' time this is run.
Flag = False

' End of block if statement.
End If

End Sub

 

[rmikesmith]

LW,

This seems like very strange behavior indeed. I have tried to duplicate it but cannot. For instance, I have set up a workbook where I have code that disables command bars in the same fashion as you. Likewise, code to enable again, which I call from within Workbook_Close. I also put a command button on the worksheet that calls a procedure to close the workbook, needed once the commandbars are disabled. After disabling the commandbars, if I click on the close button all of the initially visible command bars return. Instead, if after disabling the command bars I change the worksheet (type something into a cell, for example) then click the close button, I get the message dialog asking if I want to save. At this point, the initially visible command bars are visible again, as before.

Can you post back with more detail about how you close the workbook and the steps leading up to when you select the Cancel option, including what you mean by the &quot;Save&quot; dialog.

Regards,
M. Smith

 

[LW]

rmikesmith
Here is the code that opens the workbook and calls EverythingOff:

Private Sub Workbook_Open()

Dim CurrentSheet As Excel.Worksheet
Dim sht As Excel.Worksheet

shtBackground.Visible = xlSheetVisible
Application.ScreenUpdating = False
'These sheets should be hidden when it goes into Producion
EverythingOff
Application.ScreenUpdating = False
shtBackground.Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.Name = &quot;shtBackground&quot; Then
Else
If sht.Visible Then
Else
sht.Visible = xlSheetHidden
End If
End If
Next
shtValidClaims.Visible = xlSheetHidden
Application.ScreenUpdating = False
Cells.Select
Selection.NumberFormat = &quot;@&quot;
Range(&quot;A5&quot.Select
shtLimitedEntry.Visible = xlSheetHidden
Application.ScreenUpdating = False
Cells.Select
Selection.NumberFormat = &quot;@&quot;
Range(&quot;A2&quot.Select
ActiveWindow.FreezePanes = True
shtLimitedEntry.Activate
shtFullEntry.Visible = xlSheetHidden
boExit = False
Application.DisplayFullScreen = True
Application.ScreenUpdating = True
frmMainMenu.Show

End Sub

The EverythingOn Sub is called Here:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If boExit Then
Cancel = False
'EverythingOff
EverythingOn
Exit Sub
End If
If ThisWorkbook.ActiveSheet.Name = &quot;Background&quot; Then
Cancel = False
Else
If ThisWorkbook.Sheets(&quot;Background&quot.Visible Then
Else
ThisWorkbook.Sheets(&quot;Background&quot.Visible = xlSheetVisible
End If
ThisWorkbook.ActiveSheet.Visible = xlSheetHidden
Cancel = True
frmMainMenu.Show
If boExit Then
Cancel = False
'EverythingOff
EverythingOn
Exit Sub
Else

End If

End If

End Sub

From the Save Dialog I select Cancel.

The worksheets that are open have the Window caption, formula bar, sheet tabs, and heading displayed but not menus. I think the menus may be behind another object but I cannot tell for sure.

 

[rmikesmith]

LW,

Some additional questions. The following from your code appear to be worksheet objects: shtBackground, shtValidClaims, shtLimitedEntry, and shtFullEntry. Where do these get initiated (Set objVar = )?

I am confused by the following code:

shtBackground.Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
  If sht.Name = "shtBackground" Then
  Else
    If sht.Visible Then
    Else
      sht.Visible = xlSheetHidden
    End If
  End If
Next

Do you have a worksheet tab that displays &quot;shtBackground&quot;? And if shtBackground in the first line above is a worksheet object variable, what worksheet does it refer to? It would help if you could describe what sheets are in your workbook.

I do not believe you can have other objects obscuring the command bars. After you select Cancel, is there any open space between the Excel Titlebar and formula bar?

As a test, try this: Create a new workbook. Insert a code module and copy EverythingOn and EverythingOff into it. From Workbook_Open call EverythingOff. From Workbook_Close call EverythingOn. See what happens with the command bars when you open and close the workbook.

Regards,
M. Smith

 

[LW]

rmikesmith,
I tried your suggestion & created a new workbook. I copied the EverythingOn and EverythingOff code to a modual. I created a command button on sheet1 with Application.Quit as the code for the button.
EverythingOff is called from Workbook_Open() and EverythingOn from Workbook_BeforeClose().

The test workbook has the same problem. No menus!

Thanks for pointing out the bug in my code.(The code that confused you.)

Back to your questions. I use sht to prefix worksheets. All worksheets exist in the workbook when it is opened, so Excel creates the worksheet objects. shtBackground has a background image and is used as a backer for userforms.
The other worksheets are either tables or are used to hold data from userforms.
I agree, I think the menus are being obscured by some other GUI element.
I wonder if there is a property or a switch of some kind that controls the order of screen elements. The command commandbars(&quot;Worksheet Menu Bar&quot.Top from the intermediate window returns 0.
Something else that is strange: If I run EverythingOff then EverythingOn from the intermediate window the menus return. But this does not work if I add the EverythingOn before the EverythingOff in my code.

FYI. The behavior is the same on both NT4 SP6 Excel97 and Win2000 Excel2000.
Lynn

 

[rmikesmith]

Lynn,

I'm starting to suspect there is a problem with your installation of Excel. I just cannot reproduce the behavior you are seeing. Will you email your test workbook to me and I will run it.

Regards,
M. Smith
mike.smith@delphiauto.com

 

[LW]

With rmikesmith's help, I solved the puzzle.

By moving the call to EverythingOn from
Sub Workbook_BeforeClose to the statement directly preceding the Application.Quit statement of the Quit Command button it all works great.
Thanks again Mike!