Export Toolbar

[chris]

I created a custom toolbar that calls several macros to output certain
frequently used "ticks" at work. I was able to save the macros as an
add-in and can transfer that accordingly. However, on the toolbar, I
edited all the icons for each of the ticks by manually drawing the
wingding characters they create.

How do I export that actual toolbar and not just the macros that
support it?

Thank you.

Chris

 

[Dave Peterson]

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[chris]

Dave,

You're just the man I wanted to talk to. When I first built the
toolbar, I initially used code from Debra's site that you wrote and
just modified it to fit my purposes.

The only reason I left that approach is I couldn't figure out 2 things:
1) How to change and create custom icons for the menu items and
2) How to underline one of the characters to make them accessible with
Alt

Are you familiar with code of how to do these as I'd like to just code
it all if possible?

Thanks! From looking at your code, it's clear you know what you are
doing a heck of a lot better than I.

Chris

 

[Dave Peterson]

If you put your images in a worksheet (hidden???), you can copy and paste.

Try googling with the *excel* newsgroups for .pasteface
and you'll see lots of samples.

Here's a thread that you can weed through:

http://snipurl.com/svs7
or
http://groups.google.co.uk/group/mi...nsubject:Excel"&rnum=1&hl=en#5da1e1503eb2cd14


And try changing your caption to include an ampersand (&). That's the
accelerator key indicator.

..caption = "my &Caption here"


If you're looking for some of the built-in icons:

Doug Clancy's:
http://www.dicks-blog.com/archives/2004/11/23/faceid-viewer-addin/

John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech's:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech

 

[chris]

Dave,

You're a genius. It works. I'll paste the code below that creates the
toolbar in case someone else ever asks you the question. You'll notice
it looks very familiar to your own! I put all of this code in a
workbook and on one of the sheets, I made pictures of all the
characters. Since they are wingding characters, I just typed them all
in paint and copied them as an image into one of the sheets. I just
named them sequentially to index it with the loop.

Best of luck!

Option Explicit

Public Const ToolBarName As String = "T/M 2"
'===========================================
Sub Auto_Open()
Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()
Dim iCtr As Long

Dim MacNames As Variant, MacNames2 As Variant
Dim CapNamess As Variant, CapNamess2 As Variant

Call RemoveMenubar

MacNames = Array("TM1", _
"TM2", _
"TM3", _
"TM4", _
"TM5", _
"TM6", _
"TM7", _
"TM8", _
"TM9", _
"TM10")

CapNamess = Array("&Immaterial", _
"Tickmark &2", _
"Tickmark &3", _
"Tickmark &4", _
"Tickmark &5", _
"Tickmark &6", _
"Tickmark &7", _
"Tickmark &8", _
"Tickmark &9", _
"Tickmark 1&0")

MacNames2 = Array("SumOf1", _
"SumOf2", _
"SumOf3", _
"SumOf4", _
"SumOf5", _
"SumOf6", _
"SumOf7", _
"SumOf8", _
"SumOf9", _
"SumOf10")

CapNamess2 = Array("Sum of &1", _
"Sum of &2", _
"Sum of &3", _
"Sum of &4", _
"Sum of &5", _
"Sum of &6", _
"Sum of &7", _
"Sum of &8", _
"Sum of &9", _
"Sum of 1&0")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
With .Controls.Add(Type:=msoControlPopup, Before:=1)
.Caption = "E&xtra Tickmarks"
For iCtr = LBound(MacNames) To UBound(MacNames)
ThisWorkbook.Worksheets("Ticks").DrawingObjects("Tick "
& (iCtr + 1)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" &
MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 333
.PasteFace
End With
Next iCtr
End With
With .Controls.Add(Type:=msoControlPopup, Before:=2)
.Caption = "&Sum of:"
For iCtr = LBound(MacNames2) To UBound(MacNames2)
ThisWorkbook.Worksheets("Ticks").DrawingObjects("Sum "
& (iCtr + 1)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" &
MacNames2(iCtr)
.Caption = CapNamess2(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 333
.PasteFace
End With
Next iCtr
End With
End With
End Sub

 

[Dave Peterson]

Glad you got it working and now google has it for future searchers.

Dave,

You're a genius. It works. I'll paste the code below that creates the
toolbar in case someone else ever asks you the question. You'll notice
it looks very familiar to your own! I put all of this code in a
workbook and on one of the sheets, I made pictures of all the
characters. Since they are wingding characters, I just typed them all
in paint and copied them as an image into one of the sheets. I just
named them sequentially to index it with the loop.

Best of luck!

Option Explicit

Public Const ToolBarName As String = "T/M 2"
'===========================================
Sub Auto_Open()
Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()
Dim iCtr As Long

Dim MacNames As Variant, MacNames2 As Variant
Dim CapNamess As Variant, CapNamess2 As Variant

Call RemoveMenubar

MacNames = Array("TM1", _
"TM2", _
"TM3", _
"TM4", _
"TM5", _
"TM6", _
"TM7", _
"TM8", _
"TM9", _
"TM10")

CapNamess = Array("&Immaterial", _
"Tickmark &2", _
"Tickmark &3", _
"Tickmark &4", _
"Tickmark &5", _
"Tickmark &6", _
"Tickmark &7", _
"Tickmark &8", _
"Tickmark &9", _
"Tickmark 1&0")

MacNames2 = Array("SumOf1", _
"SumOf2", _
"SumOf3", _
"SumOf4", _
"SumOf5", _
"SumOf6", _
"SumOf7", _
"SumOf8", _
"SumOf9", _
"SumOf10")

CapNamess2 = Array("Sum of &1", _
"Sum of &2", _
"Sum of &3", _
"Sum of &4", _
"Sum of &5", _
"Sum of &6", _
"Sum of &7", _
"Sum of &8", _
"Sum of &9", _
"Sum of 1&0")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
With .Controls.Add(Type:=msoControlPopup, Before:=1)
.Caption = "E&xtra Tickmarks"
For iCtr = LBound(MacNames) To UBound(MacNames)
ThisWorkbook.Worksheets("Ticks").DrawingObjects("Tick "
& (iCtr + 1)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" &
MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 333
.PasteFace
End With
Next iCtr
End With
With .Controls.Add(Type:=msoControlPopup, Before:=2)
.Caption = "&Sum of:"
For iCtr = LBound(MacNames2) To UBound(MacNames2)
ThisWorkbook.Worksheets("Ticks").DrawingObjects("Sum "
& (iCtr + 1)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" &
MacNames2(iCtr)
.Caption = CapNamess2(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 333
.PasteFace
End With
Next iCtr
End With
End With
End Sub