custom toolbar for each sheet in workbook?

[davegb]

So, what did you decide. Dropping this aspect of the project?

See my questions above. Haven't dropped it, still struggling.

I was going

 

[Guest]

Okay, job is done.

I have created the properties for three menubars. All of them call the
common toolbars subroutine, since you have three buttons that will be common
to all of them. Additionally, I streamlined the code a little so that the
number of sub routines that would need to be copied to add a toolbar is
reduced.

Basically I pass in the number for the toolbar that I want to show, or hide,
unless I want to show all or hide all.

It appears that it is important to call the All_Bars routine prior to being
able to getting any toolbar to appear. So like in the workbook open, if you
call Toolbars.All_Bars it will create all of the toolbars, and in that
instance activate sheet1. Could revise the sheet1.activate to
activeworksheet.activate. Sounds redundant, but it forces the activation
subroutine to occur showing and hiding the appropriate sheets.

Continue looking past this initial code, I also have provided an example of
what is necessary/I used in the code of one worksheet.

Here's the code of the Toolbars module:

'Top of code here.

Option Explicit


Private Const MAX_BARS As Integer = 10

Public Sub All_Bars()
Dim CurrentSheet As Worksheet
Dim sh As Worksheet

Application.ScreenUpdating = False

Tool_Bar0_Create
Tool_Bar1_Create
Tool_Bar2_Create

Set CurrentSheet = ActiveSheet

For Each sh In Worksheets
sh.Activate
Next

CurrentSheet.Activate
Application.ScreenUpdating = True
End Sub

Public Sub All_Bars_Delete()
Dim i As Integer

i = 0

On Error GoTo Out
Err.Clear

While (Name(i) <> "" And i < Max_Tool_Bars)

Application.CommandBars(Name(i)).Delete

Out:
i = i + 1
Wend

Err.Clear
On Error GoTo 0
End Sub

Public Sub All_Bars_Hide()
Dim i As Integer
i = 0
While (Name(i) <> "" And i < Max_Tool_Bars)
On Error GoTo Hide
Err.Clear
If Application.CommandBars(Name(i)).Visible = True Then
CommandBars(Name(i)).Visible = False
End If
Hide:
i = i + 1
Wend

Err.Clear
On Error GoTo 0

End Sub

Public Function Exist(ToolName As String)
Dim FoundMenu As Variant
Dim FoundItem As Variant
Dim i As Integer

Set FoundMenu = CommandBars.ActiveMenuBar.Controls(3)
Set FoundItem = FoundMenu.Controls(3)

For i = 1 To FoundItem.Controls.Count
If (FoundItem.Controls(i).Caption = ToolName) Then
Exist = True
Exit For
'delete the name
End If
Exist = False
Next i

End Function

Public Function Max_Tool_Bars()
Max_Tool_Bars = MAX_BARS
End Function

Public Function Name(Value As Integer)
Select Case Value
Case 0
Name = "Tool Bar 0"
Case 1
Name = "Tool Bar 1"
Case 2
Name = "Tool Bar 2"
Case 3
Name = ""

Case Else
MsgBox "That Value is not yet supported"
End Select
End Function

'Might be possible to make a generic Create sub routine, but at the moment I
do
' not know how to append a number here to cause Tool_Bar1_Props to be called.
'It would be possible to create a helper sub routine, but still would have to
' add somewhere that Tool_BarX_Props exists. *shrug*
Public Sub Tool_Bar0_Create()
Tool_Bar0_Props
End Sub

Public Sub Tool_Bar1_Create()
Tool_Bar1_Props
End Sub

Public Sub Tool_Bar2_Create()
Tool_Bar2_Props
End Sub


'Generic Sub to Hide a particular toolbar number.
' No testing is done to ensure that TbrNum is within the allowable limits
Public Sub Tool_Bar_Hide(TbrNum As Integer)
On Error GoTo HideErr
Err.Clear
Application.CommandBars(Name(TbrNum)).Visible = False
Exit Sub
HideErr:
All_Bars_Delete

Err.Clear
On Error GoTo 0

End Sub

Private Sub CommonButtons(TbrNum As Integer)

Dim NewItem As Variant

' -----------Description of Button 1
Set NewItem =
Application.CommandBars(Name(TbrNum)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Mark the Selected Row(s) for Deletion"
.FaceId = 31
.OnAction = "DeleteMarker.MarkData" '""
.Style = msoButtonIconAndCaption
End With
' -----------End (Description 1)

' -----------Description of Button 1
Set NewItem =
Application.CommandBars(Name(TbrNum)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Mark the Selected Row(s) for Deletion"
.FaceId = 32
.OnAction = "DeleteMarker.MarkData" '""
.Style = msoButtonIconAndCaption
End With
' -----------End (Description 1)

' -----------Description of Button 1
Set NewItem =
Application.CommandBars(Name(TbrNum)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Mark the Selected Row(s) for Deletion"
.FaceId = 33
.OnAction = "DeleteMarker.MarkData" '""
.Style = msoButtonIconAndCaption
End With
' -----------End (Description 1)

End Sub


Private Sub Tool_Bar0_Props()
Dim NameBar As String
Dim MenuBar As CommandBar
Dim NewItem As Variant
Dim ctrl1 As Variant
Dim Found As Variant
Dim RowNum As Integer

NameBar = ToolBars.Name(0)
On Error GoTo AddErr
Err.Clear

Application.ShowToolTips = True

Set MenuBar = Application.CommandBars.Add(Name:=Name(0),
Position:=msoBarBottom, MenuBar:=False)

With MenuBar
.Protection = msoBarNoCustomize + msoBarNoChangeDock + msoBarNoMove
+ msoBarNoResize
.Visible = True
End With

Call CommonButtons(0)

' -----------Mark Selected Row(s) for Deletion
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Mark the Selected Row(s) for Deletion"
.FaceId = 31
.OnAction = "DeleteMarker.MarkData" '""
.Style = msoButtonCaption
End With
' -----------End Mark Selected Row(s) for Deletion

' -----------Move Button drop down
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With NewItem
.Caption = "&Move"
.BeginGroup = True
.TooltipText = "Move: Move the Selected Row(s) to the Delete Sheet,"
+ _
Chr(13) + Chr(10) + _
"Move the Selected Row(s) to the Keep Sheet."
End With
'Button as a part of the Move drop down
Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
With ctrl1
.DescriptionText = "Move the Selected Row(s) to the Delete Worksheet."
.Caption = "To Delete Sheet"
'.FaceId = 67
.OnAction = "Module5.Move2Del" '""
.Style = msoButtonCaption
.TooltipText = "Move the Selected Row(s) to the Delete Worksheet."
End With
'Button as a part of the Move drop down
Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
With ctrl1
.DescriptionText = "Move the Selected Row(s) to the Keep Worksheet."
.Caption = "To Keep Sheet"
'.FaceId = 270
.OnAction = "Module5.Move2Keep" '""
.Style = msoButtonCaption
.TooltipText = "Move the Selected Row(s) to the Keep Worksheet."
End With
'------------ End of Move Drop down options

'------------ Start of a New button
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Setup the Database"
'.FaceId = 2151
.OnAction = "Module4.A_SetupDatabase" '""
.Style = msoButtonCaption
End With
'------------ End of the Setup Database Button.


' -----------Print Button
' Set newItem =
Application.CommandBars(Name(0)).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
' With newItem
' .Caption = "&Print"
' .BeginGroup = True
' End With


' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "P&rint All"
' .OnAction = ""
' End With

' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 2144
' .Caption = "Print &This Sheet"
' .OnAction = ""
' End With

' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type:=msoControlButton, Id:=4)
' With NewItem
' .BeginGroup = True
'.Caption = "Enter Data"
'.FaceId = 479
'.OnAction = "ThisWorkbook.ShowNameForm" '""
' .Style = msobuttoncaption
' End With
'Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=4)
' -----------End Print Button

Err.Clear
On Error GoTo 0

Exit Sub

AddErr:
All_Bars_Delete
Resume
End Sub


Private Sub Tool_Bar1_Props()
Dim NameBar As String
Dim MenuBar As CommandBar
Dim NewItem As Variant
Dim ctrl1 As Variant
Dim Found As Variant
Dim RowNum As Integer

NameBar = ToolBars.Name(1)
On Error GoTo AddErr
Err.Clear

Application.ShowToolTips = True

Set MenuBar = Application.CommandBars.Add(Name:=Name(1),
Position:=msoBarBottom, MenuBar:=False)

With MenuBar
.Protection = msoBarNoCustomize + msoBarNoChangeDock + msoBarNoMove
+ msoBarNoResize
.Visible = True
End With

Call CommonButtons(1)

' -----------Mark Selected Row(s) for Deletion
Set NewItem =
Application.CommandBars(Name(1)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Mark the Selected Row(s) for Deletion"
.FaceId = 31
.OnAction = "DeleteMarker.MarkData" '""
.Style = msoButtonCaption
End With
' -----------End Mark Selected Row(s) for Deletion

' -----------Move Button drop down
Set NewItem =
Application.CommandBars(Name(1)).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With NewItem
.Caption = "&Move"
.BeginGroup = True
.TooltipText = "Move: Move the Selected Row(s) to the Delete Sheet,"
+ _
Chr(13) + Chr(10) + _
"Move the Selected Row(s) to the Keep Sheet."
End With
'Button as a part of the Move drop down
Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
With ctrl1
.DescriptionText = "Move the Selected Row(s) to the Delete Worksheet."
.Caption = "To Delete Sheet"
.FaceId = 67
.OnAction = "Module5.Move2Del" '""
.TooltipText = "Move the Selected Row(s) to the Delete Worksheet."
End With
'Button as a part of the Move drop down
Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
With ctrl1
.DescriptionText = "Move the Selected Row(s) to the Keep Worksheet."
.Caption = "To Keep Sheet"
.FaceId = 270
.OnAction = "Module5.Move2Keep" '""
.TooltipText = "Move the Selected Row(s) to the Keep Worksheet."
End With
'------------ End of Move Drop down options

'------------ Start of a New button
Set NewItem =
Application.CommandBars(Name(1)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Setup the Database"
.FaceId = 2151
.OnAction = "Module4.A_SetupDatabase" '""
.Style = msoButtonCaption
End With
'------------ End of the Setup Database Button.


' -----------Print Button
' Set newItem =
Application.CommandBars(Name(1)).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
' With newItem
' .Caption = "&Print"
' .BeginGroup = True
' End With


' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "P&rint All"
' .OnAction = ""
' End With

' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 2144
' .Caption = "Print &This Sheet"
' .OnAction = ""
' End With

' Set NewItem =
Application.CommandBars(Name(1)).Controls.Add(Type:=msoControlButton, Id:=4)
' With NewItem
' .BeginGroup = True
'.Caption = "Enter Data"
'.FaceId = 479
'.OnAction = "ThisWorkbook.ShowNameForm" '""
' .Style = msobuttoncaption
' End With
'Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=4)
' -----------End Print Button

Err.Clear
On Error GoTo 0

Exit Sub

AddErr:
All_Bars_Delete
Resume
End Sub


Private Sub Tool_Bar2_Props()
Dim NameBar As String
Dim MenuBar As CommandBar
Dim NewItem As Variant
Dim ctrl1 As Variant
Dim Found As Variant
Dim RowNum As Integer

NameBar = ToolBars.Name(2)
On Error GoTo AddErr
Err.Clear

Application.ShowToolTips = True

Set MenuBar = Application.CommandBars.Add(Name:=Name(2),
Position:=msoBarBottom, MenuBar:=False)

With MenuBar
.Protection = msoBarNoCustomize + msoBarNoChangeDock + msoBarNoMove
+ msoBarNoResize
.Visible = True
End With

Call CommonButtons(2)

' -----------Mark Selected Row(s) for Deletion
Set NewItem =
Application.CommandBars(Name(2)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Mark the Selected Row(s) for Deletion"
'.FaceId = 31
.OnAction = "DeleteMarker.MarkData" '""
.Style = msoButtonCaption
End With
' -----------End Mark Selected Row(s) for Deletion

' -----------Move Button drop down
Set NewItem =
Application.CommandBars(Name(2)).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With NewItem
.Caption = "&Move"
.BeginGroup = True
.TooltipText = "Move: Move the Selected Row(s) to the Delete Sheet,"
+ _
Chr(13) + Chr(10) + _
"Move the Selected Row(s) to the Keep Sheet."
End With
'Button as a part of the Move drop down
Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
With ctrl1
.DescriptionText = "Move the Selected Row(s) to the Delete Worksheet."
.Caption = "To Delete Sheet"
'.FaceId = 67
.OnAction = "Module5.Move2Del" '""
.TooltipText = "Move the Selected Row(s) to the Delete Worksheet."
End With
'Button as a part of the Move drop down
Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
With ctrl1
.DescriptionText = "Move the Selected Row(s) to the Keep Worksheet."
.Caption = "To Keep Sheet"
'.FaceId = 270
.OnAction = "Module5.Move2Keep" '""
.TooltipText = "Move the Selected Row(s) to the Keep Worksheet."
End With
'------------ End of Move Drop down options

'------------ Start of a New button
Set NewItem =
Application.CommandBars(Name(2)).Controls.Add(Type:=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Setup the Database"
.FaceId = 2151
.OnAction = "Module4.A_SetupDatabase" '""
.Style = msoButtonCaption
End With
'------------ End of the Setup Database Button.


' -----------Print Button
' Set newItem =
Application.CommandBars(Name(2)).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
' With newItem
' .Caption = "&Print"
' .BeginGroup = True
' End With


' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "P&rint All"
' .OnAction = ""
' End With

' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 2144
' .Caption = "Print &This Sheet"
' .OnAction = ""
' End With

' Set NewItem =
Application.CommandBars(Name(2)).Controls.Add(Type:=msoControlButton, Id:=4)
' With NewItem
' .BeginGroup = True
'.Caption = "Enter Data"
'.FaceId = 479
'.OnAction = "ThisWorkbook.ShowNameForm" '""
' .Style = msobuttoncaption
' End With
'Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=4)
' -----------End Print Button

Err.Clear
On Error GoTo 0

Exit Sub

AddErr:
All_Bars_Delete
Resume
End Sub

'Made a generic toolbar show routine that only requires the number of the
toolbar
' that you want to show.
Public Sub Tool_Bar_Show(TbrNum As Integer)
On Error GoTo ShowErr
Err.Clear
Application.CommandBars(Name(TbrNum)).Visible = True

Err.Clear
On Error GoTo 0
Exit Sub
ShowErr:
All_Bars

Err.Clear
On Error GoTo 0

End Sub


'End of Toolbars module code here

'Start of Sheet1 code

Option Explicit

Private Sub Worksheet_Activate()
ToolBars.All_Bars_Hide
ToolBars.Tool_Bar_Show (0)
End Sub

Private Sub Worksheet_Deactivate()
ToolBars.All_Bars_Hide
End Sub

'End of Sheet1 code


Here is my ThisWorkbook code for this:

'Start of ThisWorkbook code

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ToolBars.All_Bars_Delete
End Sub

Private Sub Workbook_Open()
ToolBars.All_Bars
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
ToolBars.All_Bars
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
ToolBars.All_Bars_Hide
End Sub



'End of ThisWorkbook code

 

[Guest]

Seems to be the order in which it runs. The thing is that after calling the
all bars and returning from that, it goes back... um somewhere, hold on.
Ohh yeah, the point at which it will error is when it tries to create the
toolbar. Thus if the toolbar already exists, what it does is get deleted
(All Bars Delete) Then is resumed, thus recreated. I couldn't find a good
way to test for the existence of the toolbar in the Commandbar list, I think
that is why I had to use that error code. (Again 3 years ago.)

In the All_Bars_Delete routine, basically if it errors out, then it keeps on
chugging to the next toolbar. Or at least that's what I intended. Ie., if
the user deleted a toolbar (View->Toolbars) then it was supposed to say, ohh
well that one is deleted get the next one. The Err.clear statement may need
to be added just after the goto portion "Out:" and before the Wend. But the
code I just published does not seem to have that problem.

As for sequencing, I included all of the code that is used to work with the
toolbars. I had a little problem getting an active sheet to run it's
activate code, so I had to change the All_Bars section a little. As written,
it would make all toolbars visible when the user left Excel and came back in.
This code will only be a problem if the number of worksheets that exist is 1
and more than one toolbar is created in the all_bars section when only one
toolbar is desired to be shown. In that instance, all toolbars defined in
the All_Bars section will be shown.

I also modified some of the button styles. Instead of being icon and
caption, I changed them to just caption. I used a three sheet workbook to
test the presentation of the menus.

I think it fits what you are wanting. Just have to modify the .actioncode,
button text and helper information, and then create additional property
sheets. One thing that was important for my usage, was that if the toolbar
was not docked, then the name entered in tbrName would show up above the
buttons. With the toolbar at the bottom of the screen, then there is no name
to be "shown" so call 'em what you want.

Enjoy.

 

[Guest]

See below for new code that I have provided you. Implemented a total of
three menu bars (Commandbars) All of them kinda' have the same properties,
so hard to tell that they are different, but you can change that yourself.

I implemented the commonbutton subroutine, to add the three buttons that you
wanted that would be common, and verified that I could get the toolbars to
appear on the appropriate sheet, and not appear when using a different
workbook.

Good luck, think you are in the final stretches.

As for the error handling, well, I implemented each one as I figured out
where it errored. Never got additional errors outside of normal usage of the
toolbars. Though that may not be true in this instance, since I never
implemented more than one toolbar before. *smirk* Tried to design for it, but
never did it.

 

[Guest]

Something that I did not explain to you or in my code. The MAX_BARS variable
is determined by the restrictions of Excel. I read somewhere that the user
was only able to create a maximum of 10 toolbars, and so I tried to implement
that here.

I realize you said you only needed like 7 or 9, I can't remember, but if
greater than 10 were necessary, the thought that I have is before, attempting
to create the 11th toolbar, one of the other toolbars is deleted (not hidden,
but actually deleted.) If that deleted toolbar is later needed, the code
should properly handle the recreation. But would have to have some way to
track how many user toolbars have been created already so that creation of
the next toolbar doesn't totally botch.

So, obviously not perfect code, but works up to the current limits of excel.

 

[Guest]

Ohh I think I understand your first question finally. All about Menubar.
Well, see this goes into the aspect of variable scope. See, I defined a
variable as MenuBar which yes is a Commandbar... Within the line that
creates the Commandbar, there is a "variable"/property name called Menubar
(Menubar:=false). Excel expects that here the use of Menubar:= is related to
a property of Commandbars, not the variable that I am setting the result.
Okay, so here probably a poor use of a variable name for understanding...

If not that, then yes, Menubar is a Toolbar. Any given toolbar, is a subset
of the Toolbars group... So you can talk about a single toolbar, or you can
talk about all toolbars. As for it being a property, I think that is an
incorrect usage of the word. It is more of an object of the Toolbars group.
(Say toolbars(10) or something.)

Hopefully that clarifies the other question that you asked.?!

The later part about calling all_bars_delete? Well, if when trying to hide
the toolbar(s) (Which is called from leaving the workbook, not the
worksheet.) the toolbar does not exist, then there is a strong possibility
that the other toolbars also do not exist. Also, if they do not exist when
hiding, then they will not exist when showing them either, so this "cleans"
up the toolbars to force them to be recreated when they are shown again later.

Get all those questions answered?

 

[davegb]

Ohh I think I understand your first question finally. All about Menubar.
Well, see this goes into the aspect of variable scope. See, I defined a
variable as MenuBar which yes is a Commandbar... Within the line that
creates the Commandbar, there is a "variable"/property name called Menubar
(Menubar:=false). Excel expects that here the use of Menubar:= is related to
a property of Commandbars, not the variable that I am setting the result.
Okay, so here probably a poor use of a variable name for understanding...

If not that, then yes, Menubar is a Toolbar. Any given toolbar, is a subset
of the Toolbars group... So you can talk about a single toolbar, or you can
talk about all toolbars. As for it being a property, I think that is an
incorrect usage of the word. It is more of an object of the Toolbars group.
(Say toolbars(10) or something.)

What I meant is the Menubar is in one instance, a toolbar object, and
in the other, Menubar:=false, a property of CommandBar.

Hopefully that clarifies the other question that you asked.?!

The later part about calling all_bars_delete? Well, if when trying to hide
the toolbar(s) (Which is called from leaving the workbook, not the
worksheet.) the toolbar does not exist, then there is a strong possibility
that the other toolbars also do not exist. Also, if they do not exist when
hiding, then they will not exist when showing them either, so this "cleans"
up the toolbars to force them to be recreated when they are shown again later.

Get all those questions answered?

Yes, I'm getting closer here.

 

[Guest]

I did just try to close my workbook, I think for the first time since I
redesigned the code... It runs into a problem that is not yet handled.. My
fix for it was to revise the Thisworkbook code to read as follows:

Option Explicit
Dim Closing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ToolBars2.All_Bars_Delete
Closing = True
End Sub

Private Sub Workbook_Open()
ToolBars2.All_Bars
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
ToolBars2.All_Bars
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If Closing = False Then
ToolBars2.All_Bars_Hide
End If
End Sub

On closing, two routines are run, the first is the close sub routine, the
second is the windowdeactivate.

I guess in my development I didn't handle what happens if you try to hide a
non-existent toolbar. The change above (usage of the Closing boolean
variable) prevents trying to hide a deleted toolbar.

 

[Guest]

Be sure you check out the code that I posted as a side thread from your
original question... I'm sorry for additional posts here and there. I
apparently had already changed the name of the module from Toolbars to
Toolbars2 so you may have to modify my final version of the ThisWorkbook code.

 

[Guest]

Good to go? Taking a break?

I did just try to close my workbook, I think for the first time since I
redesigned the code... It runs into a problem that is not yet handled.. My
fix for it was to revise the Thisworkbook code to read as follows:

Option Explicit
Dim Closing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ToolBars2.All_Bars_Delete
Closing = True
End Sub

Private Sub Workbook_Open()
ToolBars2.All_Bars
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
ToolBars2.All_Bars
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If Closing = False Then
ToolBars2.All_Bars_Hide
End If
End Sub

On closing, two routines are run, the first is the close sub routine, the
second is the windowdeactivate.

I guess in my development I didn't handle what happens if you try to hide a
non-existent toolbar. The change above (usage of the Closing boolean
variable) prevents trying to hide a deleted toolbar.