Stubborn Excel Options
 

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

Stubborn Excel Options
 

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

"VBA_Newbie79" wrote in message
...
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

Stubborn Excel Options
 

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

Regards,
Peter T

"VBA_Newbie79" wrote in message
...
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

Stubborn Excel Options
 

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

--
Regards,
Bill Renaud

Stubborn Excel Options
 

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.

"Bill Renaud" wrote:

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

--
Regards,
Bill Renaud

Stubborn Excel Options
 

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.

"Doug Glancy" wrote:

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

"VBA_Newbie79" wrote in message
...
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

Stubborn Excel Options
 

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.

"Peter T" wrote:

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

Regards,
Peter T

"VBA_Newbie79" wrote in message
...
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

Stubborn Excel Options
 

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?

"VBA_Newbie79" wrote:

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

Stubborn Excel Options
 

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" wrote in message
...
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.

"Peter T" wrote:

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

Regards,
Peter T

"VBA_Newbie79" wrote in message
...
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

Stubborn Excel Options
 

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" wrote:

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" wrote in message
...
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.

"Peter T" wrote:

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

Regards,
Peter T

"VBA_Newbie79" wrote in message
...
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

Stubborn Excel Options
 

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


"VBA_Newbie79" wrote in message
...
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" wrote:

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" wrote in message
...
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.

"Peter T" wrote:

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

Regards,
Peter T

"VBA_Newbie79" wrote in
message
...
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

Stubborn Excel Options
 

"Peter T" wrote in message
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.

If UserBar.Type < 1 And UserBar.Visible

Peter T