custom toolbar for each sheet in workbook?
 

Before I invest a lot of time, I just want to verify that what I want
to do is possible. I have a series of sheets in a workbook. I want to
have some of the tools to be the same with every sheet. But I want
certain tools to appear on the toolbar only when a specific sheet is
selected. Is this doable? I haven't done anything with VBA and toolbars
yet, but have done macros that run when a specific sheet is selected.

custom toolbar for each sheet in workbook?
 

Yes it is. I have implemented something similar. I will go ahead and
include my full Toolbar code, but I must preface it by saying that it is not
REALLY clean. I have to handle errors, because I never figured out how to
prevent them, or predetermine that they would exist to prevent them.
Basically to implement my version, when leaving the current sheet, you need
to hide the associated toolbar. When entering the new sheet, you need to
show the associated toolbar. My version will cause the toolbar to move down
and to the right until it hits a lower/right limit then begin showing it up
towards the left, although I have made my version dockable/removable. You
sound like you would want to place the toolbar at the top of the screen and
prevent a user from separating the toolbar from the menu area.

That would "fix" my problem.

Here goes and this is the entire module, so you could insert a new module,
then copy and paste the following into it. I have reused this code for
various applications, so there are several lines commented out. I also have
left some commented lessons learned in here, so that I do not duplicate the
mistake. The end of the module is designated with a very long series of -'s:

Option Explicit

' Written at Norfolk Naval Shipyard (NNSY)
' Code Written by GB
' E-mail:
' Phone #:
' Fax #:
'Version 2.1

Private Const MAX_BARS As Integer = 10

Public Sub All_Bars()
Tool_Bar1_Create
'Dim sheetActivated As Worksheet
'Set sheetActivated = ActiveSheet
Sheet1.Activate
'sheetActivated.Activate
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 = "First Tool Bar"
Case 1
Name = ""
Case Else
MsgBox "That Value is not yet supported"
End Select
End Function

Public Sub Tool_Bar1_Create()
Tool_Bar1_Props
End Sub

Public Sub Tool_Bar1_Hide()
On Error GoTo HideErr
Err.Clear
Application.CommandBars(Name(0)).Visible = False
Exit Sub
HideErr:
All_Bars_Delete

Err.Clear
On Error GoTo 0
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(0)
On Error GoTo AddErr
Err.Clear

Application.ShowToolTips = True

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

With MenuBar
.Protection = msoBarNoCustomize
.Visible = True
End With

' -----------Update Database Code Button
'Call Sheet1Code.FindNextAddRow(Sheet1)
'RowNum = Sheet1Code.GetLastAddRow

' Set Found = Sheet1.Range(Sheet1.Cells(Variables.GetSheet1RowSt art, _
' Variables.GetSheet1_Name_Col), Sheet1.Cells(RowNum, _
' Variables.GetSheet1_Name_Col)).Find("Freelance", LookIn:=xlValues,
LookAt:=xlWhole)

' Stop Below text DOES NOT WORK, do not try to use it again.
' Set Found = Sheet1.Cells(Variables.GetSheet1RowStart, _
' Variables.GetSheet1_Name_Col).Find("Freelance", LookIn:=xlValues,
Lookat:=xlWhole)

' If Not Found Is Nothing Then
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Update Code"
' .FaceId = 454
' .OnAction = "CodeUpdate.CopyNewCode" '""
' .Style = msoButtonIconAndCaption
' End With
'End If
' -----------End Update Database Code Button

' -----------Move Selected Row(s) to Delete
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Move Selected Row(s) to Delete"
' .FaceId = 67
' .OnAction = "ModuleName.Move2Del" '""
' .Style = msoButtonIconAndCaption
' End With
' -----------End Move Selected Row(s) to Delete

' -----------Move Selected Row(s) to Keep
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Move Selected Row(s) to Keep"
' .FaceId = 270
' .OnAction = "ModuleName.Move2Keep" '""
' .Style = msoButtonIconAndCaption
' End With
' -----------End Move Selected Row(s) to Keep

' -----------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 = msoButtonIconAndCaption
End With
' -----------End Mark Selected Row(s) for Deletion

' -----------Move Button
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

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 = "ModuleName.Move2Del" '""
.TooltipText = "Move the Selected Row(s) to the Delete Worksheet."
End With

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 = "ModuleName.Move2Keep" '""
.TooltipText = "Move the Selected Row(s) to the Keep Worksheet."
End With

Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Setup the Worksheet Data"
.FaceId = 2151
.OnAction = "ModuleName.A_SetupDatabase" '""
.Style = msoButtonIconAndCaption
End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 2174
' .Caption = "&Any Single Selected FY"
' .OnAction = "Fiscal.FiscalSortEnter"
' .TooltipText = "Create or modify a FY Spreadsheet."
' End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "&Year from Today"
' .OnAction = "Fiscal.CalculateYearSort"
' .TooltipText = "Update the Spreadsheet representing a year from
today."
' End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "&Upcoming Payments by Selected Month"
' .OnAction = "MonthReport.Start"
' .TooltipText = "Update the Spreadsheet representing a year from
today."
' End With

' -----------End Calculate 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 = 2174
' .Caption = "&Main Print"
' .OnAction = ""
' 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 = msoButtonIconAndCaption
' 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
'All_Bars
End Sub

Public Sub Tool_Bar1_Show()
On Error GoTo ShowErr
Err.Clear
Application.CommandBars(Name(0)).Visible = True

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

Err.Clear
On Error GoTo 0
End Sub

-------------------------------------------------------------------------------------------------------------------------------------



"davegb" wrote:

Before I invest a lot of time, I just want to verify that what I want
to do is possible. I have a series of sheets in a workbook. I want to
have some of the tools to be the same with every sheet. But I want
certain tools to appear on the toolbar only when a specific sheet is
selected. Is this doable? I haven't done anything with VBA and toolbars
yet, but have done macros that run when a specific sheet is selected.

custom toolbar for each sheet in workbook?
 

GB wrote:
Yes it is. I have implemented something similar. I will go ahead and
include my full Toolbar code, but I must preface it by saying that it is not
REALLY clean. I have to handle errors, because I never figured out how to
prevent them, or predetermine that they would exist to prevent them.
Basically to implement my version, when leaving the current sheet, you need
to hide the associated toolbar. When entering the new sheet, you need to
show the associated toolbar. My version will cause the toolbar to move down
and to the right until it hits a lower/right limit then begin showing it up
towards the left, although I have made my version dockable/removable. You
sound like you would want to place the toolbar at the top of the screen and
prevent a user from separating the toolbar from the menu area.

That would "fix" my problem.

Here goes and this is the entire module, so you could insert a new module,
then copy and paste the following into it. I have reused this code for
various applications, so there are several lines commented out. I also have
left some commented lessons learned in here, so that I do not duplicate the
mistake. The end of the module is designated with a very long series of -'s:

Option Explicit

' Written at Norfolk Naval Shipyard (NNSY)
' Code Written by GB
' E-mail:
' Phone #:
' Fax #:
'Version 2.1

Private Const MAX_BARS As Integer = 10

Public Sub All_Bars()
Tool_Bar1_Create
'Dim sheetActivated As Worksheet
'Set sheetActivated = ActiveSheet
Sheet1.Activate
'sheetActivated.Activate
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 = "First Tool Bar"
Case 1
Name = ""
Case Else
MsgBox "That Value is not yet supported"
End Select
End Function

Public Sub Tool_Bar1_Create()
Tool_Bar1_Props
End Sub

Public Sub Tool_Bar1_Hide()
On Error GoTo HideErr
Err.Clear
Application.CommandBars(Name(0)).Visible = False
Exit Sub
HideErr:
All_Bars_Delete

Err.Clear
On Error GoTo 0
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(0)
On Error GoTo AddErr
Err.Clear

Application.ShowToolTips = True

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

With MenuBar
.Protection = msoBarNoCustomize
.Visible = True
End With

' -----------Update Database Code Button
'Call Sheet1Code.FindNextAddRow(Sheet1)
'RowNum = Sheet1Code.GetLastAddRow

' Set Found = Sheet1.Range(Sheet1.Cells(Variables.GetSheet1RowSt art, _
' Variables.GetSheet1_Name_Col), Sheet1.Cells(RowNum, _
' Variables.GetSheet1_Name_Col)).Find("Freelance", LookIn:=xlValues,
LookAt:=xlWhole)

' Stop Below text DOES NOT WORK, do not try to use it again.
' Set Found = Sheet1.Cells(Variables.GetSheet1RowStart, _
' Variables.GetSheet1_Name_Col).Find("Freelance", LookIn:=xlValues,
Lookat:=xlWhole)

' If Not Found Is Nothing Then
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Update Code"
' .FaceId = 454
' .OnAction = "CodeUpdate.CopyNewCode" '""
' .Style = msoButtonIconAndCaption
' End With
'End If
' -----------End Update Database Code Button

' -----------Move Selected Row(s) to Delete
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Move Selected Row(s) to Delete"
' .FaceId = 67
' .OnAction = "ModuleName.Move2Del" '""
' .Style = msoButtonIconAndCaption
' End With
' -----------End Move Selected Row(s) to Delete

' -----------Move Selected Row(s) to Keep
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Move Selected Row(s) to Keep"
' .FaceId = 270
' .OnAction = "ModuleName.Move2Keep" '""
' .Style = msoButtonIconAndCaption
' End With
' -----------End Move Selected Row(s) to Keep

' -----------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 = msoButtonIconAndCaption
End With
' -----------End Mark Selected Row(s) for Deletion

' -----------Move Button
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

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 = "ModuleName.Move2Del" '""
.TooltipText = "Move the Selected Row(s) to the Delete Worksheet."
End With

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 = "ModuleName.Move2Keep" '""
.TooltipText = "Move the Selected Row(s) to the Keep Worksheet."
End With

Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Setup the Worksheet Data"
.FaceId = 2151
.OnAction = "ModuleName.A_SetupDatabase" '""
.Style = msoButtonIconAndCaption
End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 2174
' .Caption = "&Any Single Selected FY"
' .OnAction = "Fiscal.FiscalSortEnter"
' .TooltipText = "Create or modify a FY Spreadsheet."
' End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "&Year from Today"
' .OnAction = "Fiscal.CalculateYearSort"
' .TooltipText = "Update the Spreadsheet representing a year from
today."
' End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "&Upcoming Payments by Selected Month"
' .OnAction = "MonthReport.Start"
' .TooltipText = "Update the Spreadsheet representing a year from
today."
' End With

' -----------End Calculate 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 = 2174
' .Caption = "&Main Print"
' .OnAction = ""
' 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 = msoButtonIconAndCaption
' 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
'All_Bars
End Sub

Public Sub Tool_Bar1_Show()
On Error GoTo ShowErr
Err.Clear
Application.CommandBars(Name(0)).Visible = True

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

Err.Clear
On Error GoTo 0
End Sub

-------------------------------------------------------------------------------------------------------------------------------------



"davegb" wrote:

Before I invest a lot of time, I just want to verify that what I want
to do is possible. I have a series of sheets in a workbook. I want to
have some of the tools to be the same with every sheet. But I want
certain tools to appear on the toolbar only when a specific sheet is
selected. Is this doable? I haven't done anything with VBA and toolbars
yet, but have done macros that run when a specific sheet is selected.



Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never used before. I think this one's just beyond me at this
point.

custom toolbar for each sheet in workbook?
 

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.

For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

I didn't discuss it, but yes, I have also run into the problem of lines being
red because of the way the copy/paste works in here. But you are correct, if
it is red, either put a space and an underscore at the end of the previous
line, or move the current line to line up with the end of the previous line.
Of course, the space underscore will not work if it is captured in a quoted
line. Like:

Msgbox("This is an example of a _
broken line that will not work right. :)")

Where it should say
Msgbox("This is an example of a broken line that will not work right. :)")
'<-- although now the text statement is incorrect because it is no longer a
broken line. :) But you get the gest.


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

If anyone has ideas of how to make my toolbar module better/less prone to
having to use the error catching that I do, please assist. I wrote this
function a little over 3 years ago, and still use it without incident, but
doesn't look pretty.



"GB" wrote:

Yes it is. I have implemented something similar. I will go ahead and
include my full Toolbar code, but I must preface it by saying that it is not
REALLY clean. I have to handle errors, because I never figured out how to
prevent them, or predetermine that they would exist to prevent them.
Basically to implement my version, when leaving the current sheet, you need
to hide the associated toolbar. When entering the new sheet, you need to
show the associated toolbar. My version will cause the toolbar to move down
and to the right until it hits a lower/right limit then begin showing it up
towards the left, although I have made my version dockable/removable. You
sound like you would want to place the toolbar at the top of the screen and
prevent a user from separating the toolbar from the menu area.

That would "fix" my problem.

Here goes and this is the entire module, so you could insert a new module,
then copy and paste the following into it. I have reused this code for
various applications, so there are several lines commented out. I also have
left some commented lessons learned in here, so that I do not duplicate the
mistake. The end of the module is designated with a very long series of -'s:

Option Explicit

' Written at Norfolk Naval Shipyard (NNSY)
' Code Written by GB
' E-mail:
' Phone #:
' Fax #:
'Version 2.1

Private Const MAX_BARS As Integer = 10

Public Sub All_Bars()
Tool_Bar1_Create
'Dim sheetActivated As Worksheet
'Set sheetActivated = ActiveSheet
Sheet1.Activate
'sheetActivated.Activate
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 = "First Tool Bar"
Case 1
Name = ""
Case Else
MsgBox "That Value is not yet supported"
End Select
End Function

Public Sub Tool_Bar1_Create()
Tool_Bar1_Props
End Sub

Public Sub Tool_Bar1_Hide()
On Error GoTo HideErr
Err.Clear
Application.CommandBars(Name(0)).Visible = False
Exit Sub
HideErr:
All_Bars_Delete

Err.Clear
On Error GoTo 0
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(0)
On Error GoTo AddErr
Err.Clear

Application.ShowToolTips = True

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

With MenuBar
.Protection = msoBarNoCustomize
.Visible = True
End With

' -----------Update Database Code Button
'Call Sheet1Code.FindNextAddRow(Sheet1)
'RowNum = Sheet1Code.GetLastAddRow

' Set Found = Sheet1.Range(Sheet1.Cells(Variables.GetSheet1RowSt art, _
' Variables.GetSheet1_Name_Col), Sheet1.Cells(RowNum, _
' Variables.GetSheet1_Name_Col)).Find("Freelance", LookIn:=xlValues,
LookAt:=xlWhole)

' Stop Below text DOES NOT WORK, do not try to use it again.
' Set Found = Sheet1.Cells(Variables.GetSheet1RowStart, _
' Variables.GetSheet1_Name_Col).Find("Freelance", LookIn:=xlValues,
Lookat:=xlWhole)

' If Not Found Is Nothing Then
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Update Code"
' .FaceId = 454
' .OnAction = "CodeUpdate.CopyNewCode" '""
' .Style = msoButtonIconAndCaption
' End With
'End If
' -----------End Update Database Code Button

' -----------Move Selected Row(s) to Delete
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Move Selected Row(s) to Delete"
' .FaceId = 67
' .OnAction = "ModuleName.Move2Del" '""
' .Style = msoButtonIconAndCaption
' End With
' -----------End Move Selected Row(s) to Delete

' -----------Move Selected Row(s) to Keep
' Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
' With NewItem
' .BeginGroup = True
' .Caption = "Move Selected Row(s) to Keep"
' .FaceId = 270
' .OnAction = "ModuleName.Move2Keep" '""
' .Style = msoButtonIconAndCaption
' End With
' -----------End Move Selected Row(s) to Keep

' -----------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 = msoButtonIconAndCaption
End With
' -----------End Mark Selected Row(s) for Deletion

' -----------Move Button
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

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 = "ModuleName.Move2Del" '""
.TooltipText = "Move the Selected Row(s) to the Delete Worksheet."
End With

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 = "ModuleName.Move2Keep" '""
.TooltipText = "Move the Selected Row(s) to the Keep Worksheet."
End With

Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
With NewItem
.BeginGroup = True
.Caption = "Setup the Worksheet Data"
.FaceId = 2151
.OnAction = "ModuleName.A_SetupDatabase" '""
.Style = msoButtonIconAndCaption
End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 2174
' .Caption = "&Any Single Selected FY"
' .OnAction = "Fiscal.FiscalSortEnter"
' .TooltipText = "Create or modify a FY Spreadsheet."
' End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "&Year from Today"
' .OnAction = "Fiscal.CalculateYearSort"
' .TooltipText = "Update the Spreadsheet representing a year from
today."
' End With

' Set ctrl1 = NewItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480
' .Caption = "&Upcoming Payments by Selected Month"
' .OnAction = "MonthReport.Start"
' .TooltipText = "Update the Spreadsheet representing a year from
today."
' End With

' -----------End Calculate 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 = 2174
' .Caption = "&Main Print"
' .OnAction = ""
' End With

' Set ctrl1 = newItem.Controls.Add(Type:=msoControlButton, Id:=1)
' With ctrl1
' .FaceId = 480

custom toolbar for each sheet in workbook?
 

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

Well, actually you seem to be doing quite well with this... Sorry,
Toolbars.... As it is used in here? Is the name of the Module. :) You can
kick yourself later...

I have put all of the TOOLBAR (Commandbar) code into a module on it's own.
This way I can easily export/copy the code to another project. :) It is self
contained, and requires no additional references..
Umm.. I'll look into the absence of TOOLBAR1_HIDE, but I thought there was
something similar to that in the code...

As for the TBRNAME function... Yes if I give it say zero then it gives me
back the toolbar named as it is in the text. If you go ahead and create a
toolbar using the code, then go into the EXCEL sheet, go to View, Toolbars,
you will see the name of your toolbar has been added to the list. That name
should be unique for any toolbars that are to be used on the worksheet, so
that you do not have conflicting toolbars. :) Make sense?

You are correct, using someone elses can be painstaking, which I think in
this case a few days of understanding what probably took me a few weeks to
write, is not all that bad. :) Sorry for the lack of documentation, I was a
lone programmer with a signficantly short deadline. *smirk* But it worked,
still works, and is vastly distributed. :)

Have a good day. I had to come in on my day off.. *smirk*

"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)



"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

GB wrote:
Well, actually you seem to be doing quite well with this... Sorry,
Toolbars.... As it is used in here? Is the name of the Module. :) You can
kick yourself later...

I have put all of the TOOLBAR (Commandbar) code into a module on it's own.
This way I can easily export/copy the code to another project. :) It is self
contained, and requires no additional references..
Umm.. I'll look into the absence of TOOLBAR1_HIDE, but I thought there was
something similar to that in the code...

Ok, makes sense.


As for the TBRNAME function... Yes if I give it say zero then it gives me
back the toolbar named as it is in the text. If you go ahead and create a
toolbar using the code, then go into the EXCEL sheet, go to View, Toolbars,
you will see the name of your toolbar has been added to the list. That name
should be unique for any toolbars that are to be used on the worksheet, so
that you do not have conflicting toolbars. :) Make sense?

You are correct, using someone elses can be painstaking, which I think in
this case a few days of understanding what probably took me a few weeks to
write, is not all that bad. :) Sorry for the lack of documentation, I was a
lone programmer with a signficantly short deadline. *smirk* But it worked,
still works, and is vastly distributed. :)

Have a good day. I had to come in on my day off.. *smirk*

Not sure if smirking at the office is allowable... :)
Thanks, this makes sense.


"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

True, I believe there is some rule about smirking... :)

Anyway, not that I am walking away from this subject, but it has become a
wee bit difficult to keep up with this thread now. For some reason when I
search for threads that have GB in them, this one does not pop up unless I
search for GB toolbar.

I can still field questions, but I have been trying to keep up with this one
because it's obviously near and dear to my heart. I think this is the first
time that I have published this code for someone to use and as I said I wrote
it like 3 years ago. Additionally, I don't frequent here somewhat because I
can get obsessive about helping other people out. It's kind of fun to guide
others to that aha point. I have a feeling that if you broaden your view a
little bit more that you will be able to move forward with this task and
others. By that I mean stepping back a moment... You appear to be able to
analyze well, like trying to figure out what toolbar.whatever meant and just
needed to "look around the corner" to get it. I commend you on your effort
and persistence.

I'll continue to review this topic for additional comments or questions. Of
course if you find a way to improve on the code, by all means post it back.
I wish I could get the thing to work without having all the error trapping.
Ohh speaking of which. I had some thoughts.

Sometimes programmers will add error trapping into their code to do
something specific no matter where the error occurs. I did not capture that
fact in this module. By that I mean, prior to reassigning the actions to be
performed on error, the current actions should be captured so that they can
be restored when I want to "clear" the actions for an error.

Also, I believe it is possible to tell a toolbar (commandbar) to be
positioned at a certain point on the screen. I didn't go through the effort
of forcing that, but you may want to. I would recommend using the registry
as a location to store the location desired/changed. Implementation would
store the current position of the toolbar prior to hiding/deleting it, such
that on each load/show of the toolbar it would appear at the last location.
As a default I would recommend not too far from the top left portion of the
screen, because if they go to a differently arranged computer, the toolbar
may show up off of the screen. This would also imply that when showing the
toolbar it should at least be partially visible in the window so that the
toolbar could be grabbed and moved into full view.

Just some ideas. The current setup pretty much will ensure that it is on
the screen somewhere, but it's location moves depending on how often it gets
deleted/created.


"davegb" wrote:


GB wrote:
Well, actually you seem to be doing quite well with this... Sorry,
Toolbars.... As it is used in here? Is the name of the Module. :) You can
kick yourself later...

I have put all of the TOOLBAR (Commandbar) code into a module on it's own.
This way I can easily export/copy the code to another project. :) It is self
contained, and requires no additional references..
Umm.. I'll look into the absence of TOOLBAR1_HIDE, but I thought there was
something similar to that in the code...

Ok, makes sense.


As for the TBRNAME function... Yes if I give it say zero then it gives me
back the toolbar named as it is in the text. If you go ahead and create a
toolbar using the code, then go into the EXCEL sheet, go to View, Toolbars,
you will see the name of your toolbar has been added to the list. That name
should be unique for any toolbars that are to be used on the worksheet, so
that you do not have conflicting toolbars. :) Make sense?

You are correct, using someone elses can be painstaking, which I think in
this case a few days of understanding what probably took me a few weeks to
write, is not all that bad. :) Sorry for the lack of documentation, I was a
lone programmer with a signficantly short deadline. *smirk* But it worked,
still works, and is vastly distributed. :)

Have a good day. I had to come in on my day off.. *smirk*

Not sure if smirking at the office is allowable... :)
Thanks, this makes sense.


"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!

custom toolbar for each sheet in workbook?
 

First thing. No, it is not possible to create a toolbar that will follow the
application/user, be shown only for the application in question, and not
require VBA code.

As for your toolbars. You basically have/want this: Three commands to
exist on all toolbars (easy to handle), and additional commands to be
appended/removed as necessary. You have toolbars that are specific to your
application (Therefore the toolbar module must be in that excel document.)
The toolbars must be destroyed at the closure of your application, or else
those toolbars would be available to the user when opening any excel
document, and could really throw that document for a big loop.

If you implement the presentation/hiding of the toolbars using the workbook
and worksheet activate/deactivate and workbook open and close sub-routines as
I described, then your toolbar(s) will be appropriately shown for the
applicable sheet(s).

What I would do from this point is, determine what buttons you want on each
toolbar (a, b, c, f, n, etc..) figure out which code is to be run for each
of those buttons. Determine what icon if any you want associated to
that/those buttons. Then at least modify the Tool_bar1_Prop to show just the
first three buttons. (Those will be common to all tool bars.)

Then copy the tool_bar1_prop code the number of times that you will have a
"different" toolbar. If 3 sheets are only going to use tool_bar1, then you
do not have to create a new toolbar for each sheet, just reuse/reshow
tool_bar1. Rename each Tool_Bar(x)_prop code to correspond to a toolbar
number you like. Perhaps Tool_Bar1 should be changed to Tool_Bar0 so that it
corresponds to the number sent to tbrName *shrug*.

In tbrName, assign a unique name to each toolbar. Ensure you know which
number corresponds to which toolbar (ie. the select case statement.)
Copy the three other utility functions: Hide, Create, and show. Change the
number supplied to tbrName to correspond to the applicable toolbar being
hidden, created, and shown. Also number those three functions as appropriate
for your numbering scheme.

Then go back and revise the toolbar prop section to include the additional
buttons that you want. I think that if you do not include the .FaceId it will
not present an icon. If that is not true, then there is a way to show a
button with text only, but again I do not have access to the VBA helpfiles to
tell you what that option is. If you can't ferret it out, then it doesn't
hurt anything really to have that added icon.

As for position... Well, that's one that I don't know off of the top of my
head how to accomplish. To force it at the bottom would be like docking it
at the bottom. At the moment I have the drawing toolbar docked at the bottom
of my window, and a similar action should be programmatically possible. I
agree, you do not want to remove/revise the standard menu options or
menubars... Basically if they have done something with them on their own,
that's their business, not yours. :) Minimal impact by you as the
programmer. Sure you could force certain bars to appear on load up, but
that's really going the extra mile.

As for creating them on the fly vs. precreating them... Writing the code in
the property sub function is precreating them to be shown on the fly... Ie.,
using the method described above regarding activation, opening and closing of
worksheets and the workbook. The error handling will ensure that the
toolbars are "available" by the code to be shown. As each of the 9 sheets is
selected, the appropriate toolbar(s) are shown and you as the programmer need
to do nothing other than say, when I get into this document and this
particular worksheet, I want this toolbar shown. When I leave this document
i.e., go to a Word document, or Internet explorer, I don't want this or any
of these toolbars to be shown. When I come back to the document, I want the
toolbar that is applicable to this worksheet to be shown.

The hard part is determining what you want on the toolbar and setting it up
in the Properties section. As you can see, that is the section that is most
commented by use of the apostrophe. Sometimes I have implemented drop downs,
sometimes drop downs off of drop downs. So I have kept "notes" about how to
do that. When I want to implement it, I just uncomment the line(s) and
revise the text/action code as appropriate. Sometimes I'll go through the
effort to modify the faceid.

At anyrate, I think you are in the final stretch, and have most of the tools
and knowhow available to implement your goal. The code you have worked
through, does everything you want it to do except force the toolbar to be
docked in a certain location.
And to do that, I have briefly reviewed the object library. It appears that
there is a command/function/sub that will at least provide you the Left, Top,
height, width, and position of the toolbar. I didn't see anything that said
docked or or undocked, or whatever, but I bet it is there under some other
name. With that information you could at least initially position the
toolbar (Top of toolbar = Height of window + Top of window - Height of
toolbar and the left position of toolbar = Left of window) and then may be
able to prevent the user from moving the toolbar by one of the commandbar
commands.

Sorry, I could be more assistive if I had the help files available, but they
were not installed on this computer.

Don't give up now... :)


"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del

custom toolbar for each sheet in workbook?
 

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del

custom toolbar for each sheet in workbook?
 

Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.


"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the

custom toolbar for each sheet in workbook?
 

So, what did you decide. Dropping this aspect of the project? I was going
to see if I could implement what we have talked about. I think it would be a
matter of a few minutes. I'll post the code when I'm done.

"davegb" wrote:

Before I invest a lot of time, I just want to verify that what I want
to do is possible. I have a series of sheets in a workbook. I want to
have some of the tools to be the same with every sheet. But I want
certain tools to appear on the toolbar only when a specific sheet is
selected. Is this doable? I haven't done anything with VBA and toolbars
yet, but have done macros that run when a specific sheet is selected.

custom toolbar for each sheet in workbook?
 

GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the

custom toolbar for each sheet in workbook?
 

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

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

I was going
to see if I could implement what we have talked about. I think it would be a
matter of a few minutes. I'll post the code when I'm done.

"davegb" wrote:

Before I invest a lot of time, I just want to verify that what I want
to do is possible. I have a series of sheets in a workbook. I want to
have some of the tools to be the same with every sheet. But I want
certain tools to appear on the toolbar only when a specific sheet is
selected. Is this doable? I haven't done anything with VBA and toolbars
yet, but have done macros that run when a specific sheet is selected.

custom toolbar for each sheet in workbook?
 

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


"davegb" wrote:

Before I invest a lot of time, I just want to verify that what I want
to do is possible. I have a series of sheets in a workbook. I want to
have some of the tools to be the same with every sheet. But I want
certain tools to appear on the toolbar only when a specific sheet is
selected. Is this doable? I haven't done anything with VBA and toolbars
yet, but have done macros that run when a specific sheet is selected.

custom toolbar for each sheet in workbook?
 

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. :)

"davegb" wrote:


GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:

custom toolbar for each sheet in workbook?
 

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.

"davegb" wrote:


GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:

custom toolbar for each sheet in workbook?
 

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.


"GB" wrote:

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

custom toolbar for each sheet in workbook?
 

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" wrote:


GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:

custom toolbar for each sheet in workbook?
 

GB wrote:
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.


"davegb" wrote:


GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:

custom toolbar for each sheet in workbook?
 

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.



"GB" wrote:

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

custom toolbar for each sheet in workbook?
 

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.


"davegb" wrote:


GB wrote:
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.


"davegb" wrote:


GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


GB wrote:
Sorry, I may have answered this part of the question before, but might as
well answer it he

Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:

In ThisWorkbook:

I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.

If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.

However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.

Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) < ""; NumToolBars = NumToolBars + 1; loop)

Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.

I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.

I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.

Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.

At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using. :)

How's that? :)

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't