Run Time Error with Toolbars

  • Thread starter Thread starter Full Monty
  • Start date Start date
F

Full Monty

Created a workbook with 12 sheets for some clients.

Have the following VBA code used to hide all the toolbars when opening
And unhide when closing the workbook.

Dim aryCBs

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long

For i = LBound(aryCBs) To UBound(aryCBs)
Application.CommandBars(aryCBs(i)).Visible = True
Next i
ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim i As Long

Application.ScreenUpdating = False
Application.DisplayFormulaBar = False
ReDim aryCBs(0)
For Each oCB In Application.CommandBars
If oCB.Visible Then
If oCB.Name <> "Worksheet Menu Bar" Then
ReDim Preserve aryCBs(i)
aryCBs(i) = oCB.Name
oCB.Visible = False
i = i + 1
End If
End If
Next oCB
Application.ScreenUpdating = True

End Sub

A single customer says that he gets a run time error when closing th
workbook. And it appears in the following code:
Application.CommandBars(aryCBs(i)).Visible = True

He is running the same version of Excel (97) as I am. I have tried t
duplicate this error and cannot!\

Anybody have an idea as to why he is the only one with this problem ou
of all my customers?

Appreciate the help
 
One possibility, He/she has something installed on his PC that resets global
variables or he works in the VBE him/herself and resets global variables.

However, I that were the case, I would expect the error to occur on the
lbound(aryCBs). but it still may be the case.
 
Tom,
Thanks for the reply. I have no worries about him tinkering with VBA
He can barely navigate through simple Excel activities like cel
formatting!

As for something resetting global variables, he is using the Exce
program on a fairly new Toshiba Laptop instead of a PC. If that makes
difference.

If I come up with anything, I will be sure to post another reply s
everyone can see what is happening!

Thanks Again
 
Back
Top