Clear then Recreate existing toolbars?

[JeffT]

Hi

Is it possible to record which toolbars are open on a computor & close them all. Then after running my own file with its own toolbar which I've created, reopen the toolbars which were open on closing my file.

The problem I have is different people have different toolbars open when they start up Excel. For my file I need them to use the buttons from My toolbar some of which have the same face as standard toolbars eg Print. I can close all the existing toolbars but don't know which ones to open upon closing my file.

Thanks for your help

Regards

Jeff T

[Zack Barresse]

Hi Jeff,

Maybe you could adapt something like the following, it works for me...


In ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ShowActiveBars
End Sub

Private Sub Workbook_Open()
HideActiveBars
End Sub[/vba]
In a Standard Module:
[vba]Sub HideActiveBars()
Dim cb As CommandBar, i As Long, x As Long
On Error GoTo noSheet
Worksheets("CommandBars").Activate
GoTo hasSheet
noSheet:
Worksheets.Add Before:=Sheets(1)
ActiveSheet.Name = "CommandBars"
Err.Clear
hasSheet:
x = 1
With Application
For i = 1 To .CommandBars.Count
If .CommandBars(i).Name = "Worksheet Menu Bar" Then GoTo SkipMe
If .CommandBars(i).Visible = True Then
Range("A" & x).Value = .CommandBars(i).Name
x = x + 1
.CommandBars(i).Visible = False
End If
SkipMe:
Next i
Columns(1).EntireColumn.AutoFit
End With
End Sub

Sub ShowActiveBars()
On Error GoTo endMe
Worksheets("CommandBars").Activate
Dim i As Long, x As Long
i = Range("A65536").End(xlUp).Row
For x = 1 To i
Application.CommandBars(Range("A" & x).Value).Visible = True
Next x
endMe:
End Sub

[Richie(UK)]

Hi Jeff,

Here's some code from Jim Rech that does as you ask:


'Jim Rech Excel MVP
'I just list the user's toolbars in a worksheet for restoring
'when my app is done. The range "UserToolbars" is a single cell on a sheet
'with the codename "shConfig":

Sub HideUserCBs()
Dim CurrCB As Object, Counter As Integer
With shConfig.Range("UserToolbars")
For Each CurrCB In Application.CommandBars
If CurrCB.Visible = True And CurrCB.Type <> 1 Then
'Type 1 = a menubar which errors if hidden
.Offset(Counter).Value = CurrCB.Name
CurrCB.Visible = False
Counter = Counter + 1
End If
Next
.Offset(Counter).ClearContents
End With
End Sub

Sub RestoreCBs()
Dim Counter As Integer, CurrTB As String
On Error Resume Next
'In case we're closing XL and a CB has already been closed,
'perhaps by the workbook that created it.
GoTo BeginHere
Do
Application.CommandBars(CurrTB).Visible = True
Counter = Counter + 1
BeginHere:
CurrTB = shConfig.Range("UserToolbars").Offset(Counter).Value
Loop Until CurrTB = ""
End Sub

However, a word of caution - just because you can do something it doesn't mean that you should. In general, users of Excel absolutely HATE having their working environment messed with, especially if they already have other workbooks open. I have done this sort of thing in my early days of coding, and regretted it. I would never do it now. By all means add you own menu, but think twice about removing everything else.

[JeffT]

Thanks Firefytr & Ritche.

I must admit I baulked at the Firefytr code way above my head, but Ritchie I think I can actually see how it works. Still I take your point about changing users toolbars. My only problem is to make sure they use My print icon not the one on the standard toolbar. Mine picks out the cells which need printing and ensures pagination is correct. I've just figured out how to put my own Icon faces on so may do that instead and refer to that in the Help file.

As an alternative is there any way to make an Icon in an existing toolbar run my macro when my file is the one being used and not when you switch to others? This would be better in view of your comments and hadn't thought of it till now. I usually play around with code until I find a solution and only bother the forum when I'm totally stumped. so sorry if this has been answered before or is simple.

Regards and thanks for all the answers

Jeff T

[Zack Barresse]

so sorry if this has been answered before or is simple.
No worries. ;)


My only problem is to make sure they use My print icon not the one on the standard toolbar. Mine picks out the cells which need printing and ensures pagination is correct. I've just figured out how to put my own Icon faces on so may do that instead and refer to that in the Help file.

Maybe it would be easier if you used a different icon instead of the native print icon as to distinguish yours apart from the original. Or put text next to yours..

John Walkenbach has a nifty little add-in to help you distinguish different FaceId values. Just note, they do differ from Excel 2000 to Excel 2002 (XP), but not by much (in case you use different versions and some do not appear in 2002). A link to that information is here (http://j-walk.com/ss/excel/tips/tip67.htm).


As an alternative is there any way to make an Icon in an existing toolbar run my macro when my file is the one being used and not when you switch to others?

I don't know if there is a way to do that, not to the existing anyway, but even if you could I would strongly recommend against an action such as this. Making the toolbar(s) Invisible is one thing, changing them is something completely different.


I usually play around with code until I find a solution and only bother the forum when I'm totally stumped.

Kudos for you! :D That's totally awesome that you can do that. A lot of people will not even try things on their own. I find that's when I learn the most, when I'm doing trial and error. :yes

[Jacob Hilderbrand]

Can't we just disable all the command bars, then enable them later. That way all the command bars that were visible will still be visible once that are enabled again.


Option Explicit

Sub HideCommandBars()
Dim Cmd As CommandBar
For Each Cmd In CommandBars
Cmd.Enabled = False
Next
End Sub

Sub ShowCommandBars()
Dim Cmd As CommandBar
For Each Cmd In CommandBars
Cmd.Enabled = True
Next
End Sub

[johnske]

Can't we just disable all the command bars, then enable them later. That way all the command bars that were visible will still be visible once that are enabled again.
Yes,

Essentially, that's what I do - I dont like either the inbuild XL or Word command bars setup, so I have a macro that simply hides all the standard ones and this actuates another that builds and shows my custom ones.

But I only have myself to consider, in this case, a macro to reverse the procedure (as Jacob suggests) would keep all users happy. (My 2p)

John :bink:

[CBrine]

Jeff,

Here's a example of the code I use to do as you requested.

HTH
Cal

[JeffT]

Thanks for all your replies. Lots to think about. I'll first of all make my own icons and load them on my toolbar. If people still do the wrong thing I'll give hiding the toolbars a try. I've downloaded / copied all the code, so thanks for that.

I may play around and try altering the actions of the buttons on the existing toolbars later , doing my usual lemon test method of programming. :)

Regards

Jeff T