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