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

--
Message posted from http://www.ExcelForum.com

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

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
"Geo Siggy " wrote in message
...
| 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 from http://www.ExcelForum.com/
|

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

-----Original Message-----
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 from http://www.ExcelForum.com/

.

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)

"Patrick Molloy" wrote in message
...
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

-----Original Message-----
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 from http://www.ExcelForum.com/

.

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

--
Message posted from http://www.ExcelForum.com

Hi,
I forgot,
I also used the declaration

Public arrayCB() As String

... Sigg

--
Message posted from http://www.ExcelForum.com