Hi,

On opening th workbook include the following statement:

UserToolBars(xlOff)

On closing include the following statement:

UserToolBars(xlOn)

Put the following in a standard module

Sub UserToolBars(State)
Static UserToolBars As New Collection
Dim UserBar
If State = xlOn Then
For Each UserBar In Application.CommandBars
If UserBar.Type < 1 And UserBar.Visible Then
UserToolBars.Add UserBar
UserBar.Visible = False
End If
Next UserBar
Else
For Each UserBar In UserToolBars
UserBar.Visible = True
Next UserBar
End If
End Sub

xlOn and xlOff are built in excel constants
All toolbars except the main menu abr are hidden/restored.
HTH

Don

"Peter Rooney" wrote in message
...
Good morning, all!

I'm trying to write a routine which will hide all the toolbars that are
visible whenever a workbook is open, then be able to re-open all the
toolbars
that it hid, when the workbook is closed.

I tried to accomplish this by writing the CommandBar numbers of all the
open
CommandBars to an array - this all works OK, but when I actually get to
the
third loop in my macro, and the code tries to close the first visble
toolbar,
I get
"Method 'Visible' of object 'CommandBars' failed" on the line marked with
three asterisks below.

The first loop runs purely to allow me to redimension the array to make it
contain just enough elements to store the numbers of the open toolbars.
The second loop stores the numbers of the open CommandBars to the array.

I'm going to need to keep these values static, so that I can unhide the
appropriate CommandBars when I'm done, but can anyone help me out with why
this line of code won't work, please. 3 hours struggling with this is
quite
enough - over to the experts!

Thanks in advance

Pete



Sub HideVisibleCommandBars()

Dim VCBCount As Integer
Dim VCBArray() As Integer
Dim LoopCounter As Integer
Dim TotalBarCount As Integer


'Determine how many CommandBars are visible

For LoopCounter = 1 To Application.CommandBars.Count
TotalBarCount = TotalBarCount + 1
If Application.CommandBars(LoopCounter).Visible = True Then
'MsgBox (Application.CommandBars(LoopCounter).Name)
VCBCount = VCBCount + 1
End If
Next
MsgBox ("There are " & TotalBarCount & " command bars of which " &
VCBCount & " are visible!")

'Redimension the array based on how many visible CommandBars were detected

ReDim VCBArray(VCBCount) As Integer

'Store CommandBar numbers of Visible CommandBars to array

VCBCount = 0
For LoopCounter = 1 To Application.CommandBars.Count
If Application.CommandBars(LoopCounter).Visible = True Then
VCBCount = VCBCount + 1
VCBArray(VCBCount) = LoopCounter
MsgBox (VCBArray(VCBCount))
End If
Next

'Hide Visible CommandBars based on numbers stored in array

For LoopCounter = 1 To VCBCount
MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _
"VCBArray Element " & LoopCounter & " contains " &
VCBArray(LoopCounter))
Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '***
Next

End Sub