Decalring Workbook level arrays.
 

How do I go about declaring a workbook array that I can use at any point
during a sub?

The problem I have is this...

I'm hiding a users toolbars and creating some of my own, for a specific
application I'm developing. When they open the Workbook I assign all enabled
commandbars to an array so I can do what I want with them. When they close
the workbook I want to re-enable all of the ones I disabled earlier, but
can't see how to do it.

I'm using Office 2000.

Any help would be greatly appreciated.

Decalring Workbook level arrays.
 

Hi DaveO,

When they close
the workbook I want to re-enable all of the ones I disabled earlier, but
can't see how to do it.


I would use a txt file for this:

Dim oBar As CommandBar
Dim lFile As Long
Dim sFilename As String
Dim bEnabled As Boolean
Dim bVisible As Boolean

sFilename = ThisWorkbook.Path & "\Commandbars.ini"
lFile = FreeFile
Open sFilename For Output As lFile
On Error Resume Next
For Each oBar In Application.CommandBars
bEnabled = oBar.Enabled
bVisible = oBar.Visible
Write #lFile, oBar.Name, bEnabled, bVisible
oBar.Visible = False
oBar.Enabled = False
Next
Close #lFile

Then later on:

Dim oBar As CommandBar
Dim lFile As Long
Dim sFilename As String
Dim sTemp As String
Dim bEnabled As Boolean
Dim bVisible As Boolean
lFile = FreeFile
sFilename = ThisWorkbook.Path & "\Commandbars.ini"
If Dir(sFilename) = "" Then
'Settings lost, at least make sure things can be accessed
For Each oBar In Application.CommandBars
oBar.Enabled = True
Next
Exit Sub
End If
Open sFilename For Input As lFile
On Error Resume Next
For Each oBar In Application.CommandBars
Input #lFile, sTemp, bEnabled, bVisible
If oBar.Name = sTemp Then
oBar.Enabled = bEnabled
oBar.Visible = bVisible
End If
Next
Close #lFile

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Decalring Workbook level arrays.
 

I use the following code to remove and restore the User's Toolbars.

Sub UserToolBars(State)
Static UserToolBars As New Collection
Dim UserBar
If State = xlOn Then
For Each UserBar In Application.CommandBars
If UserBar.Type < 1 And UserBar.Visible Then
UserToolBars.Add UserBar
UserBar.Visible = False
End If
Next UserBar
Else
For Each UserBar In UserToolBars
UserBar.Visible = True
Next
End If
End Sub

Call the routine with
UserToolBars ( xlOn )
to hide and save the toolbars, and with
UserToolBars ( xlOff )
to restore them..

You need to handle your own separately of course

"DaveO" wrote in message
...
How do I go about declaring a workbook array that I can use at any point
during a sub?

The problem I have is this...

I'm hiding a users toolbars and creating some of my own, for a specific
application I'm developing. When they open the Workbook I assign all
enabled
commandbars to an array so I can do what I want with them. When they close
the workbook I want to re-enable all of the ones I disabled earlier, but
can't see how to do it.

I'm using Office 2000.

Any help would be greatly appreciated.