Export Toolbar

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

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

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

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/mic...a1e1503eb2cd14


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.*****-blog.com/archives/2...-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 wrote:
>
> 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 wrote:
> > 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 wrote:
> > >
> > > 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

--

Dave Peterson

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 wrote:
> 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/mic...a1e1503eb2cd14
>
>
> 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.*****-blog.com/archives/2...-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 wrote:
> >
> > 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 wrote:
> > > 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 wrote:
> > > >
> > > > 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
>
> --
>
> Dave Peterson

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

chris wrote:
>
> 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 wrote:
> > 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/mic...a1e1503eb2cd14
> >
> >
> > 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.*****-blog.com/archives/2...-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 wrote:
> > >
> > > 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 wrote:
> > > > 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 wrote:
> > > > >
> > > > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson