command bars

[Geo Siggy]

Hello,

I tried the following code (I get it from somebody, don't remember wh
…) to hide the normally used commandbars when entering an applicatio
and reinstall the same used commandbars again when leaving the program
The hiding works fine, but when leaving the application, the followin
message comes:

"Runtime error 9, Index outside valid array."

Whats wrong with the code.

The code:

Public arrayCB As Variant

Private Sub Workbook_Open()

Dim myCB As CommandBar
Dim ii As Long

ReDim arrayCB(0)
For Each myCB In Application.CommandBars
If myCB.Visible Then
ReDim Preserve aryCBs(ii)
arrayCB(ii) = myCB.Name
myCB.Visible = False
ii = ii + 1
End If
Next myCB
Application.CommandBars("Worksheet Menu Bar").Visible = False

With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ii As Long

For ii = LBound(arrayCB) To UBound(arrayCB)
Application.CommandBars(arrayCB(ii)).Visible = True
Next ii

End Sub


… thanks Sigg

 

[fred]

The code:

Public arrayCB As Variant

Try using: Private arrayCB() as String

Private Sub Workbook_Open()

Dim myCB As CommandBar
Dim ii As Long

ReDim arrayCB(0)
For Each myCB In Application.CommandBars
If myCB.Visible Then
ReDim Preserve aryCBs(ii)

I presume this should be ReDim Preserve arrayCB(ii)

Hope this helps
Fred

 

[Jim Rech]

Put this code in a regular module:

Public arrayCB() As String

Sub HideOpenToolbars()
Dim myCB As CommandBar
Dim ii As Long

ReDim arrayCB(0)
For Each myCB In Application.CommandBars
If myCB.Visible Then
If myCB.Type <> msoBarTypeMenuBar Then
ReDim Preserve arrayCB(ii)
arrayCB(ii) = myCB.Name
myCB.Visible = False
ii = ii + 1
End If
End If
Next myCB
Application.CommandBars("Worksheet Menu Bar").Enabled = False

With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub

Private Sub UnhideToolbars()
Dim ii As Long
For ii = LBound(arrayCB) To UBound(arrayCB)
Application.CommandBars(arrayCB(ii)).Visible = True
Next ii
Application.CommandBars("Worksheet Menu Bar").Enabled = True
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
End Sub

And in the ThisWorkbook module, call it:

Private Sub Workbook_Open()
HideOpenToolbars
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UnhideToolbars
End Sub


--
Jim Rech
Excel MVP
| Hello,
|
| I tried the following code (I get it from somebody, don't remember who
| …) to hide the normally used commandbars when entering an application
| and reinstall the same used commandbars again when leaving the program.
| The hiding works fine, but when leaving the application, the following
| message comes:
|
| "Runtime error 9, Index outside valid array."
|
| Whats wrong with the code.
|
| The code:
|
| Public arrayCB As Variant
|
| Private Sub Workbook_Open()
|
| Dim myCB As CommandBar
| Dim ii As Long
|
| ReDim arrayCB(0)
| For Each myCB In Application.CommandBars
| If myCB.Visible Then
| ReDim Preserve aryCBs(ii)
| arrayCB(ii) = myCB.Name
| myCB.Visible = False
| ii = ii + 1
| End If
| Next myCB
| Application.CommandBars("Worksheet Menu Bar").Visible = False
|
| With Application
| DisplayFormulaBar = False
| DisplayStatusBar = False
| End With
|
| End Sub
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
|
| Dim ii As Long
|
| For ii = LBound(arrayCB) To UBound(arrayCB)
| Application.CommandBars(arrayCB(ii)).Visible = True
| Next ii
|
| End Sub
|
|
| … thanks Siggy
|
|
| ---
| Message posted
|

 

[Patrick Molloy]

First, explicitly dim all your variables - at the top of
the module:
Option Explicit

then when you compile you'll see that your variable names
are screwed for one

 

[Bob Phillips]

and Worksheet Menu Bar uses the Enabled property not visible.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Geo Siggy]

Hi,
I used the following code (from Jim) for hiding and unhiding th
commandbars,
but still the following error message appears:
" Runtime error 9, Index outside valid array" marking the following
code line:
"For ii = LBound(arrayCB) To UBound(arrayCB)"

Whats still wrong ?

Here is the used code:

Sub HideOpenToolbars()
Dim myCB As CommandBar
Dim ii As Long
ReDim arrayCB(0)
For Each myCB In Application.CommandBars
If myCB.Visible Then
If myCB.Type <> msoBarTypeMenuBar Then
ReDim Preserve arrayCB(ii)
arrayCB(ii) = myCB.Name
myCB.Visible = False
ii = ii + 1
End If
End If
Next myCB
Application.CommandBars("Worksheet Menu Bar").Enabled = True
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub


Sub UnhideToolbars()
Dim ii As Long
For ii = LBound(arrayCB) To UBound(arrayCB)
Application.CommandBars(arrayCB(ii)).Visible = True
Next ii
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Visible = True
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
End Sub


Private Sub Workbook_Open()
HideOpenToolbars
...
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UnhideToolbars
...
End Sub


Whats wrong ?
Any idea ?
Thanks
Sigg

 

[Geo Siggy]

Hi,
I forgot,
I also used the declaration

Public arrayCB() As String

... Sigg