Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export Toolbar
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export Toolbar
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export Toolbar
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...e1503eb2 cd14 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/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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export Toolbar
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, Befo=1) .Caption = "E&xtra Tickmarks" For iCtr = LBound(MacNames) To UBound(MacNames) ThisWorkbook.Worksheets("Ticks").DrawingObjects("T ick " & (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, Befo=2) .Caption = "&Sum of:" For iCtr = LBound(MacNames2) To UBound(MacNames2) ThisWorkbook.Worksheets("Ticks").DrawingObjects("S um " & (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...e1503eb2 cd14 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/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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export Toolbar
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, Befo=1) .Caption = "E&xtra Tickmarks" For iCtr = LBound(MacNames) To UBound(MacNames) ThisWorkbook.Worksheets("Ticks").DrawingObjects("T ick " & (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, Befo=2) .Caption = "&Sum of:" For iCtr = LBound(MacNames2) To UBound(MacNames2) ThisWorkbook.Worksheets("Ticks").DrawingObjects("S um " & (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...e1503eb2 cd14 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/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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving custom toolbars with macros to work on other computers | Excel Discussion (Misc queries) | |||
Missing Macro Buttons on Toolbar | Excel Discussion (Misc queries) | |||
Adjusting toolbar size, restore toolbar | Excel Discussion (Misc queries) | |||
CANT SAVE TOOLBAR CHANGES EXCEL 2003) | Excel Discussion (Misc queries) | |||
Toolbar problem | Excel Discussion (Misc queries) |