Stubborn Excel Options

[VBA_Newbie79]

I have adopted some code I received from this forum to create a personalized
toolbar, hide all menus and other toolbars, as well remove the formula bar,
worksheet tabs, and column/row headings.

The code runs in the ThisWorkbook module in both the WindowActivate and
WindowDeactivate events. It works fine flipping between the application and
other Excel files, but refuses to turn the formula bar, worksheet tabs, and
column/row headings back on when I close the file.

I tried including the code in a BeforeClose event, but that doesn't make a
difference. The formula bar, worksheet tabs, and column/row headings will
only turn on via the Tools > Options menu. Have I corrupted something or is
this a "quirk" of Excel?

Below is my code:
----------------------------------------------------
Private Sub Workbook_Windowactivate(ByVal Wn As Excel.Window)

Application.ScreenUpdating = False
Call CreateMenubar
UserToolBars (xlOn)

With ActiveWindow
.DisplayWorkbookTabs = False
.DisplayHeadings = False
End With

With Application
.DisplayFormulaBar = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.ScreenUpdating = True
End With

End Sub
----------------------------------------------------
Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)

Application.ScreenUpdating = False
Call RemoveMenubar
UserToolBars (xlOff)

With ActiveWindow
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With

With Application
.DisplayFormulaBar = True
.CommandBars("Worksheet Menu Bar").Enabled = True
.ScreenUpdating = True
End With

End Sub

 

[Doug Glancy]

Is it that the state with the tabs turned off was saved, but that you're not
saving it after your deactivate routine turns them back on?

Doug

 

[Peter T]

Try moving your code from the window events to the Workbook_Activate &
Workbook_Deactivate events respectively

Regards,
Peter T

 

[Bill Renaud]

You might try using the Visible property of the toolbar, instead of the
Enabled property.

 

[VBA_Newbie79]

Bill,
The way I understand it, is the Visible property works for all the toolbars
except the "Worksheet Menu Bar" which contains the File menu, the Edit menu,
etc. Thanks for your response, though. It's best to look at all angles.

 

[VBA_Newbie79]

Doug,
I tried changing my close routine to ThisWorkbook.Close SaveChanges:=True,
instead of False, but it still didn't work. Good thought, though. I'll keep
that in mind for future problems. Thanks.

 

[VBA_Newbie79]

Peter,
Unfortunately the results were even worse. For some reason my UserToolBars
function doesn't work correctly in the Workbook_Activate and
Workbook_Deactivate events. It doesn't fix my first problem, either.

 

[VBA_Newbie79]

I have received some wonderful suggestions so far, but perhaps I haven't
offered enough information. The formula bar, worksheet tabs, and column/row
headings are all displayed before the application loads. When I close the
application, it appears that the worksheet tabs and column/row headings are
default and actually do turn back on by themselves when creating a new
workbook. The formula bar still won't turn on without going to Tools,
Options, though.

When I step through the code under Workbook_WindowDeactivate, it shows
DisplayWorkbookTabs = False, but won't turn on with DisplayWorkbookTabs =
True. The same is the case for DisplayHeadings and DisplayFormulaBar. While
it shows CommandBars("Worksheet Menu Bar").Enabled = False, it will turn the
Menu Bar back on if I equal to True. Is there an enabled property for the
other options?

What am I missing?

 

[Peter T]

Something's wrong, should work fine in the activate/deactivate events.

Not sure what your "UserToolBars" does but suggest start a small test wb and
try the (de)activate events again. Keep adding new enable/disable or visible
false/true items until one doesn't work correctly then post back.

Regards,
Peter T

 

[VBA_Newbie79]

Peter,
Interestingly enough, I didn't receive any errors with the test wb. Is it
possible that some feature of UserToolBars is causing this? This has been
the most stable and effective way I have found to determine all of the
toolbars someone has activated, and then re-activate's them when appropriate.
----------------------------------
Sub UserToolBars(State)

Static UserToolBars As New Collection
Dim UserBar

Application.ScreenUpdating = False
If State = xlOn Then
For Each UserBar In Application.CommandBars
If UserBar.Type <> 1 And UserBar.Visible And UserBar.Name <>
"Handbook" Then
UserToolBars.Add UserBar
UserBar.Visible = False
End If
Next UserBar
Else
For Each UserBar In UserToolBars
UserBar.Visible = True
Next UserBar
End If

Application.ScreenUpdating = True

End Sub
----------------------------------

 

[Peter T]

In the nicest possible way, as I don't like my settings incorrectly
restored, if someone sent me a workbook with a routine like UserToolBars I'd
bin it!

There are any number of reasons why the variables holding the original
settings (in this case the static collection) may get destroyed. Also, why
are you resetting virtually all bars visible = true when they may or may not
have been visible originally. IOW there's every possibility some settings
may be restored incorrectly or, in the case of the collection being
destroyed, no settings restored at all.

One way to hide most bars is simply
Application.DisplayFullScreen = True ' False

Otherwise, IMO the only way is to save relevant original application
settings to cells or registry then reapply same when done. I'd suggest save
to cells on a hidden sheet. Even that is not foolproof.

Regards,
Peter T

PS, here's an example, adapt and add any other app settings as required.
After testing change "Sheet1" to that of a hidden sheet, say xlVeryhidden

Sub test()
Dim bShow As Boolean

' false save settings & hide / true re-show
bShow = False ' True

ToggleBars bShow

End Sub

Sub ToggleBars(bReset As Boolean)
Dim i As Long, u As Long
Dim cbr As CommandBar
Dim arr()

ReDim arr(1 To Application.CommandBars.Count, 1 To 2)

If Not bReset Then
If ThisWorkbook.Worksheets("Sheet1").Range("A1") Then
'normally this shouldn't occur, implies bars were not reset
' so reset before saving current settings
ToggleBars True
End If
For Each cbr In Application.CommandBars
If cbr.Type = msoBarTypeNormal Then
u = u + 1
arr(u, 1) = cbr.Name
arr(u, 2) = cbr.Visible
cbr.Visible = False
End If
Next

With ThisWorkbook.Worksheets("Sheet1")
.Range("A1") = u
If u Then
.Range("A2").Resize(u, 2).Value = arr
End If
End With

Else
With ThisWorkbook.Worksheets("Sheet1")
u = .Range("A1")
If u Then
arr = .Range("A2").Resize(u, 2).Value
End If
.Range("A1").CurrentRegion.ClearContents
End With

If u Then
With Application.CommandBars
For i = 1 To u
.Item(arr(i, 1)).Visible = arr(i, 2)
Next
End With
End If
End If

End Sub

 

[Peter T]

Also, why
are you resetting virtually all bars visible = true when they may or may not
have been visible originally.

Ah, I see you only added the bar to the collection if it was visible.

Peter T