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/

.