Hiding all visible toolbars by writing their numbers to an array

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"Peter Rooney" wrote:

> 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
>

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



"Don Lloyd" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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,
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" wrote:

> 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
>
>
>
> "Don Lloyd" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

Doh,

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

Thanks very much - all working fine, now!

Pete



"Toppers" wrote:

> 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" wrote:
>
> > 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
> >
> >
> >
> > "Don Lloyd" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >

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


"Don Lloyd" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>