Enable toolbars at close
 

I created a macro rich file that when it is opened all the
active toolbars that the user has on there screen are
turned off. What I want to do is be able to make them
active again once the user closes the spreadsheet. This is
the method I am using right now.
............................
Option Explicit
Public ToolbarArray(20) As String

Sub HideAllToolbars()
Dim TBVar As Variant
Dim Counter As Integer
Counter = 1
For Each TBVar In Application.Toolbars
If TBVar.Visible = True Then
ToolbarArray(Counter) = TBVar.Name
TBVar.Visible = False
Counter = Counter + 1
End If
Next
End Sub

Sub RestoreToolbars()
Dim TBVar As Variant
For Each TBVar In ToolbarArray
If (TBVar < "") Then
Application.Toolbars(TBVar).Visible = True
End If
Next
End Sub
...........................
This works fine if the user only opens and closes the
program with running any of the macros in the spreadsheet,
but once any macro is performed the memory of what
toolbars are shut off is wiped out and the restore Toolbar
sub does nothing when initiated.

Is there a way around this? If I have to I am willing to
recall each toolbar indivisually, but I am not sure how to
do that?

Any help is appreciated.
Pete

Enable toolbars at close
 

Application.Toolbars?

Try

Application.Commandbars

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pete" wrote in message
...
I created a macro rich file that when it is opened all the
active toolbars that the user has on there screen are
turned off. What I want to do is be able to make them
active again once the user closes the spreadsheet. This is
the method I am using right now.
...........................
Option Explicit
Public ToolbarArray(20) As String

Sub HideAllToolbars()
Dim TBVar As Variant
Dim Counter As Integer
Counter = 1
For Each TBVar In Application.Toolbars
If TBVar.Visible = True Then
ToolbarArray(Counter) = TBVar.Name
TBVar.Visible = False
Counter = Counter + 1
End If
Next
End Sub

Sub RestoreToolbars()
Dim TBVar As Variant
For Each TBVar In ToolbarArray
If (TBVar < "") Then
Application.Toolbars(TBVar).Visible = True
End If
Next
End Sub
..........................
This works fine if the user only opens and closes the
program with running any of the macros in the spreadsheet,
but once any macro is performed the memory of what
toolbars are shut off is wiped out and the restore Toolbar
sub does nothing when initiated.

Is there a way around this? If I have to I am willing to
recall each toolbar indivisually, but I am not sure how to
do that?

Any help is appreciated.
Pete

Enable toolbars at close
 

Bob I don't follow your instruction. I also have a similar
situation where I want to disable certain toolbars when my
spreadsheet opens.

(ie. Standard, formatting, reviewing, visual basic....)

How do you turn these off and on via code at will?
Please advise.
James

-----Original Message-----
Application.Toolbars?

Try

Application.Commandbars

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pete" wrote in
message
...
I created a macro rich file that when it is opened all
the
active toolbars that the user has on there screen are
turned off. What I want to do is be able to make them
active again once the user closes the spreadsheet. This
is
the method I am using right now.
...........................
Option Explicit
Public ToolbarArray(20) As String

Sub HideAllToolbars()
Dim TBVar As Variant
Dim Counter As Integer
Counter = 1
For Each TBVar In Application.Toolbars
If TBVar.Visible = True Then
ToolbarArray(Counter) = TBVar.Name
TBVar.Visible = False
Counter = Counter + 1
End If
Next
End Sub

Sub RestoreToolbars()
Dim TBVar As Variant
For Each TBVar In ToolbarArray
If (TBVar < "") Then
Application.Toolbars(TBVar).Visible = True
End If
Next
End Sub
..........................
This works fine if the user only opens and closes the
program with running any of the macros in the
spreadsheet,
but once any macro is performed the memory of what
toolbars are shut off is wiped out and the restore
Toolbar
sub does nothing when initiated.

Is there a way around this? If I have to I am willing to
recall each toolbar indivisually, but I am not sure how
to
do that?

Any help is appreciated.
Pete


.

Enable toolbars at close
 

CommandBars("Formatting").Visible = False

to get a list

rw = 1
for each cbar in Application.Commandbars
cells(rw,1).Value = cbar.name
rw = rw + 1
Next


For the formula and/or statusbar

Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True

"James" wrote in message
...
Bob I don't follow your instruction. I also have a similar
situation where I want to disable certain toolbars when my
spreadsheet opens.

(ie. Standard, formatting, reviewing, visual basic....)

How do you turn these off and on via code at will?
Please advise.
James

-----Original Message-----
Application.Toolbars?

Try

Application.Commandbars

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pete" wrote in
message
...
I created a macro rich file that when it is opened all
the
active toolbars that the user has on there screen are
turned off. What I want to do is be able to make them
active again once the user closes the spreadsheet. This
is
the method I am using right now.
...........................
Option Explicit
Public ToolbarArray(20) As String

Sub HideAllToolbars()
Dim TBVar As Variant
Dim Counter As Integer
Counter = 1
For Each TBVar In Application.Toolbars
If TBVar.Visible = True Then
ToolbarArray(Counter) = TBVar.Name
TBVar.Visible = False
Counter = Counter + 1
End If
Next
End Sub

Sub RestoreToolbars()
Dim TBVar As Variant
For Each TBVar In ToolbarArray
If (TBVar < "") Then
Application.Toolbars(TBVar).Visible = True
End If
Next
End Sub
..........................
This works fine if the user only opens and closes the
program with running any of the macros in the
spreadsheet,
but once any macro is performed the memory of what
toolbars are shut off is wiped out and the restore
Toolbar
sub does nothing when initiated.

Is there a way around this? If I have to I am willing to
recall each toolbar indivisually, but I am not sure how
to
do that?

Any help is appreciated.
Pete


.

Enable toolbars at close
 

Try This:

Sub DIS_ENAB_CB()
Dim CB As CommandBar
For Each CB In Application.CommandBars
CB.Visible 'Enable = True 'False
Next CB
End Sub

-----Original Message-----
Bob I don't follow your instruction. I also have a
similar
situation where I want to disable certain toolbars when
my
spreadsheet opens.

(ie. Standard, formatting, reviewing, visual basic....)

How do you turn these off and on via code at will?
Please advise.
James

-----Original Message-----
Application.Toolbars?

Try

Application.Commandbars

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pete" wrote in
message
.. .
I created a macro rich file that when it is opened all
the
active toolbars that the user has on there screen are
turned off. What I want to do is be able to make them
active again once the user closes the spreadsheet.
This
is
the method I am using right now.
...........................
Option Explicit
Public ToolbarArray(20) As String

Sub HideAllToolbars()
Dim TBVar As Variant
Dim Counter As Integer
Counter = 1
For Each TBVar In Application.Toolbars
If TBVar.Visible = True Then
ToolbarArray(Counter) = TBVar.Name
TBVar.Visible = False
Counter = Counter + 1
End If
Next
End Sub

Sub RestoreToolbars()
Dim TBVar As Variant
For Each TBVar In ToolbarArray
If (TBVar < "") Then
Application.Toolbars(TBVar).Visible = True
End If
Next
End Sub
..........................
This works fine if the user only opens and closes the
program with running any of the macros in the
spreadsheet,
but once any macro is performed the memory of what
toolbars are shut off is wiped out and the restore
Toolbar
sub does nothing when initiated.

Is there a way around this? If I have to I am willing
to
recall each toolbar indivisually, but I am not sure
how
to
do that?

Any help is appreciated.
Pete


.

.