Need to hide and show the customized toolbars in Excel 4

[Ankor]

Hello,

I have an issue hiding and showing toolbars in Excel. The problem is that in order to make my application work properly I have to get rid of the default toolbars on the spreadsheet. Then, when the user closes my application, the tollbars should be placed back. But if the user had additional buttons on the standard toolbars, these buttons would be deleted. In other words no customization is kept. In order to hide and show the toolbars I use:
Application.CommandBars("Standard&quot.Visible = HideShow
Application.CommandBars("Formatting&quot.Visible = HideShow
where HideShow can be true or false. Is there any way to solve my problem?

Thank you.

 

[RobBroekhuis]

From your question I can't tell if you want the customized entries to stay, or if you want to undo them. Setting the visible property shouldn't have any effect on the toolbars (besides their presence on screen) - the customizations should still be there when your application terminates.
Rob

 

[Ankor]

I want the customized entries to stay, and they don't even when I use the visible property. I have a feeling that when I set, for example, the Standard toolbar to be visible, I always call the default one, not the one I customized. I tried to write the Sub with the simple code:
Sub Check()
Application.CommandBars("Standard&quot.Visible = False
Application.CommandBars("Standard&quot.Visible = True
End Sub
Everything works here, all customizations are saved. But whenever I use the real code, I cannot keep the customized buttons.
This is the code I use in my file:
Sub HideShowToolbars(HideShow As Boolean)
Dim bar As CommandBar
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = True
Application.DisplayFormulaBar = HideShow
Application.CommandBars("Worksheet Menu Bar&quot.Enabled = HideShow
Application.CommandBars("Standard&quot.Visible = HideShow
Application.CommandBars("Formatting&quot.Visible = HideShow
If HideShow = True Then
Application.CommandBars("MyModel&quot.Visible = False
Else
For Each bar In Application.CommandBars
If bar.Visible = True Then
bar.Visible = False
End If
Next
Application.CommandBars("MyModel&quot.Visible = True
End If
End Sub
Can you please tell me what is wrong here? I am very new to VBA, and probably don't understand how all these properties work. Thanks.
Ankor

 

[RobBroekhuis]

Your code looks fine. Your customizations are on the "standard" toolbar? I don't see why they would be undone by anything you do in this procedure. Tell us exactly what happens (what kind of customization, which toolbars, etc).
Rob

 

[Ankor]

The problem is that in order to open MyModel toolbar, I have to open an .xlb file first and then assign macros to every button in the new toolbar. The .xlb file contains everything: Standard, Formatting and MyModel. Standard and Formatting are in the default appearance, and they overwrite the customized ones. If I create an .xlb with just MyModel toolbar, it simply deletes all other toolbars on the spreadsheet. Then when I close the file, I can call Standard and Formatting, but they will be the default ones again.
No matter what buttons I add and what toolbar I change, all customization goes away when I open my .xlb. So the problem is not in my sub, it happens even before I run the code I posted above. Any ideas?
Thanks for your help.

 

[RobBroekhuis]

I've not used .xlb files to hold custom toolbars (how do you do that?). I would think that, when you're done with your application, you should reload Excel.xlb to keep the previous customized versions of the toolbars. Or would that be too simple?
Rob

 

[kylua]

I have an example workbook which I think will show you how to solve the problem. Send you request to colin@kylua.com and I'll send you it.
I seem to recall it saves all the previous toolbars as is, and then restores them. Never used it tho.

And one day, I'll put these files on a website. Honest.

 

[Ankor]

kulua, that would be wonderful to have your sample file. Unfortunately, I cannot receive the files from the outside sources, our firewall will not let me do that. Could you explain how you save the previous settings?

Rob, you gave me an idea! If Excel.xlb is updated only when I close Excel, then it probably stores the previous version of the toolbars, correct? If yes, then I just need to restore it instead of making the default toolbars visible.
Concerning the customized .xlb, you just need to copy your Excel.xlb into another folder and rename it. I have it copied to the server, and whenever the user openes my common Excel report, he gets it with the toolbar I customized especially for it. You can have several toolbars in one folder and assign them to the particular reports, so the users won't be able to do what they should not do .

 

[Loomah]

Dunno if this matches your requirements, I've never used it with cusom or customised toolbars but...

in a module

Option Explicit

Sub InitView()
    SetWindow xlOn
    SetMenu
End Sub

Sub ExitView()
    SetWindow xlOff
    ZapMenu
End Sub

Sub SetMenu()
    Dim myBar As CommandBar
    Dim myButton As CommandBarButton
    ZapMenu
    Set myBar = CommandBars.Add(Name:="OCM", _
                Position:=msoBarTop, _
                MenuBar:=True)
    Set myButton = myBar.Controls.Add(msoControlButton)
        myButton.Style = msoButtonCaption
        myButton.Caption = "E&xit"
        myButton.OnAction = "ExitOCM"
        myBar.Protection = msoBarNoMove + msoBarNoCustomize
        myBar.Visible = True
End Sub

Sub ZapMenu()
    On Error Resume Next
        CommandBars("OCM").Delete
End Sub

Sub SetWindow(State)
    Static myOldState
    Application.ScreenUpdating = False
    On Error Resume Next
        If State = xlOn Then
            Application.DisplayFormulaBar = False
            Application.DisplayStatusBar = False
        'CHANGED TO SPECIFY XLOFF
        ElseIf State = xlOff Then
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
        End If
End Sub

Sub ExitOCM()
    ' added lines to restore formula & status bars
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWorkbook.Close (False)
End Sub

in the workbook code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ExitView
End Sub

Private Sub Workbook_Open()
    InitView
End Sub

You might also want to look at utilising the Activate and Deactivate events.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[RobBroekhuis]

Ankor,
I'm still curious - how do you get Excel to use your custom .xlb file? I thought the Excel only read excel.xlb when it first started up (and you're right that it only saves it upon quitting the application) - is there a command to "install" a new .xlb on the fly?
Rob

 

[Loomah]

Dunno if this matches your requirements, I've never used it with cusom or customised toolbars but...

in a module

Option Explicit

Sub InitView()
    SetWindow xlOn
    SetBars xlOn
    SetMenu
End Sub

Sub ExitView()
    SetWindow xlOff
    SetBars xlOn
    ZapMenu
End Sub

Sub SetMenu()
    Dim myBar As CommandBar
    Dim myButton As CommandBarButton
    ZapMenu
    Set myBar = CommandBars.Add(Name:="OCM", _
                Position:=msoBarTop, _
                MenuBar:=True)
    Set myButton = myBar.Controls.Add(msoControlButton)
        myButton.Style = msoButtonCaption
        myButton.Caption = "E&xit"
        myButton.OnAction = "ExitOCM"
        myBar.Protection = msoBarNoMove + msoBarNoCustomize
        myBar.Visible = True
End Sub

Sub ZapMenu()
    On Error Resume Next
        CommandBars("OCM").Delete
End Sub

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

Sub SetWindow(State)
    Static myOldState
    Application.ScreenUpdating = False
    On Error Resume Next
        If State = xlOn Then
            Application.DisplayFormulaBar = False
            Application.DisplayStatusBar = False
        'CHANGED TO SPECIFY XLOFF
        ElseIf State = xlOff Then
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
        End If
End Sub

Sub ExitOCM()
    ' added lines to restore formula & status bars
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWorkbook.Close (False)
End Sub

in the workbook code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ExitView
End Sub

Private Sub Workbook_Open()
    InitView
End Sub

This is just the basics, pick through and see what you may need. It's not fully tested here as therre is a kind of mixture of two apps! You might also want to look at utilising the Activate and Deactivate events.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[kylua]

My file is the one that Loomah is quoting. The relevant bit is:

Sub SetBars(State)
Static myOldBars As New Collection
Dim myBar

If State = xlOn Then
For Each myBar In Application.CommandBars
If myBar.Type <> 1 And myBar.Visible Then
myOldBars.Add myBar
myBar.Visible = False
End If
Next myBar
Else
For Each myBar In myOldBars
myBar.Visible = True
Next myBar
End If
End Sub

Where setbars is called with xlon or xloff to either store or restore the menus. It should work with custom bars as it stores all that are there. Apart from if the type is one?

 

[Ankor]

Rob,
You don't need to install the toolbar, you just open an .xlb file you saved under a different name:
Workbooks.Open &quot;\\ServerName\Reports\yourbarname.xlb&quot;
When you do that, your current toolbars will be replaced with what you have in the customized file. If you created a new custom toolbar, you will also need to assign the macros to each button every time you open your file:
Application.CommandBars(&quot;MyModel&quot.Controls(1).OnAction = &quot;YourSubName&quot;
Then you do the same for each control in the toolbar.

 

[RobBroekhuis]

Okay, that's actually quite simply - thanks for pointing out that I can do that. It would appear that it also makes your task of restoring the menus quite simple - just open the original excel.xlb. But you probably already came to that conclusion.
Rob

 

[Ankor]

kylua / Loomah,
Do I have to recreate my custom toolbar each time I open the file?
Thanks.

Rob,
You are welcome. And yes, I came to the same conclusion. Thanks to you .

Ankor

 

[Loomah]

Ankor
In a word, yes!
This code is something I worked on with someone on this site some time ago. The full thing is a bit bulky as it creates a full menu bar. I'm impressed with the idea of using a .xlb file - a lot simpler and something I'll remember just incase I get a job!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Ankor]

Sorry, I disappeared.

Rob,
Everything worked except of one thing. If you have users with Excel 97, you are in trouble. A default .xlb file is stored in a different place. As a result, I tryed to create a toolbar in the code.

Loomah,
Again, I had a problem with Excel 97. If your file is saved in Excel 2000, it crashes Excel 97 while creating a toolbar even though the code is absolutely identical.

I guess we found all ways of using the toolbars. Thank you!!! I think I should reistall Excel on old computers, and my problem will be solved.