Hiding all visible toolbars by writing their numbers to an array

[Peter Rooney]

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)).Visible = False '***
Next

End Sub

 

[Peter Rooney]

I think I solved part of the problem - the first visible command bar was the
worksheet menu bar (CommandBar ID 1), which can't be hidden in this way
(apparently), If I changed my loop counter to start from 2, then all the
other toolbars are successfully hidden.

This leads me to 2 further questions, then:

1. How do I distinguish between toolbars and menu bars to (in this case)
start my loop from 1 but ignore the Worksheet menu bar?
2. How to I preserve the commandbar IDs that I stored to my array so that I
can later go back and use the array values to redisplay the toolbars that
they refer to- is it something to do with STATIC..?

Thanks again

Pete

 

[Don Lloyd]

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]

Don,

This looks good, except that the toolbars go off when I CLOSE the workbook!
Here are my Workbook_Open and Workbook_BeforeClose

Private Sub Workbook_Open()
UserToolBars (xlOff)
End Sub

Private Sub Workbook_BeforeClose(cancel As Boolean)
UserToolBars (xlOn)
End Sub

The other code is in a normal module

Any thoughts?

Pete

 

[Toppers]

Peter,
You have Xlon/xlOff the wrong way round.


Private Sub Workbook_Open()
UserToolBars (xlOn)
End Sub

Private Sub Workbook_BeforeClose(cancel As Boolean)
UserToolBars (xlOff)
End Sub


HTH

 

[Peter Rooney]

Doh,

You'd have thought I'd have tried that, wouldn't you..?

Thanks very much - all working fine, now!

Pete

 

[Peter Rooney]

Don,

Yep, got my xlo/xloff the wrong way round!

Your userbar.type was a good one I didn't know about previously, too!

All working fine now