Run Time Error with Toolbars
 

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

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

Run Time Error with Toolbars
 

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.

--
Regards,
Tom Ogilvy

"Full Monty " wrote in message
...
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 the
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 to
duplicate this error and cannot!\

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

Appreciate the help!


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

Run Time Error with Toolbars
 

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

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