View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
DAnny DAnny is offline
external usenet poster
 
Posts: 13
Default 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



.

.