Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting Page Setup on a workbook level | Excel Discussion (Misc queries) | |||
Can I create multi-level passwords for the same workbook | Excel Worksheet Functions | |||
Level of protection | Excel Discussion (Misc queries) | |||
trouble returning a workbook level Name object | Excel Programming | |||
Why, when I create workbook-level name does it jump it to Sheet-level ? | Excel Programming |