Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
I'm working on a custom toolbar for a workbook I'm building (plenty of
buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
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) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. ker_01 wrote: I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
Thank you Dave- it looks like an add-in will be the best way to deploy my
toolbar. I'll use code to install and load (and unload) the add-in from my actual workbook. The one piece I'm still struggling with is whether there is any way to keep the custom button icons that I've already developed. I edited my icons within the Excel icon editor, so I don't have them saved anywhere as a separate file. Is my only option to recreate each 16x16 (bitmap) icon in an image editing program, or is there any way to grab a button image (perhaps by VBA) and save that image to the worksheet so I can copy it into my new add-in? Thanks for any advice, Keith "Dave Peterson" wrote in message ... 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) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. ker_01 wrote: I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
I just found a utility on Andy Pope's site that appears to save an existing
button icon- I'll try it out, and if I have problems I'll post again. Thanks! Keith "Dave Peterson" wrote in message ... 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) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. ker_01 wrote: I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
Here's one that uses pictures on a worksheet -- nicely named!
Option Explicit Public Const ToolBarName As String = "MyToolbarName" 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 Dim CapNames As Variant Dim TipText As Variant Dim PictNames As Variant Dim PictWks As Worksheet Call RemoveMenubar MacNames = Array("aaa", _ "bbb") CapNames = Array("AAA Caption", _ "BBB Caption") TipText = Array("AAA tip", _ "BBB tip") PictNames = Array("Pic1", "Pic2") Set PictWks = ThisWorkbook.Worksheets("Pictures") With Application.CommandBars.Add .Name = ToolBarName .Left = 200 .Top = 200 .Protection = msoBarNoProtection .Visible = True .Position = msoBarFloating For iCtr = LBound(MacNames) To UBound(MacNames) PictWks.Pictures(PictNames(iCtr)).Copy With .Controls.Add(Type:=msoControlButton) .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr) .Caption = CapNames(iCtr) .Style = msoButtonIconAndCaption .PasteFace .TooltipText = tip_text(iCtr) End With Next iCtr End With End Sub Sub AAA() MsgBox "aaa" End Sub Sub BBB() MsgBox "bbb" End Sub The AAA and BBB subs are just stubs. You can put your macro code that does the real work there--or call your macros from them. ker_01 wrote: Thank you Dave- it looks like an add-in will be the best way to deploy my toolbar. I'll use code to install and load (and unload) the add-in from my actual workbook. The one piece I'm still struggling with is whether there is any way to keep the custom button icons that I've already developed. I edited my icons within the Excel icon editor, so I don't have them saved anywhere as a separate file. Is my only option to recreate each 16x16 (bitmap) icon in an image editing program, or is there any way to grab a button image (perhaps by VBA) and save that image to the worksheet so I can copy it into my new add-in? Thanks for any advice, Keith "Dave Peterson" wrote in message ... 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) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. ker_01 wrote: I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
Thanks again Dave! I've adapted this code, and pasted my icons on a hidden
sheet. Everything is working (almost) perfectly; the only downside is that the pasted icons don't seem to have a transparent background anymore, and the background gray used is a little different from the toolbar color. This is an internal project so I'm not worried, and if it bugs me too much I'll just figure out which color matches the toolbar grey and go backfill the icons manually. Thanks!! Keith "Dave Peterson" wrote in message ... Here's one that uses pictures on a worksheet -- nicely named! Option Explicit Public Const ToolBarName As String = "MyToolbarName" 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 Dim CapNames As Variant Dim TipText As Variant Dim PictNames As Variant Dim PictWks As Worksheet Call RemoveMenubar MacNames = Array("aaa", _ "bbb") CapNames = Array("AAA Caption", _ "BBB Caption") TipText = Array("AAA tip", _ "BBB tip") PictNames = Array("Pic1", "Pic2") Set PictWks = ThisWorkbook.Worksheets("Pictures") With Application.CommandBars.Add .Name = ToolBarName .Left = 200 .Top = 200 .Protection = msoBarNoProtection .Visible = True .Position = msoBarFloating For iCtr = LBound(MacNames) To UBound(MacNames) PictWks.Pictures(PictNames(iCtr)).Copy With .Controls.Add(Type:=msoControlButton) .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr) .Caption = CapNames(iCtr) .Style = msoButtonIconAndCaption .PasteFace .TooltipText = tip_text(iCtr) End With Next iCtr End With End Sub Sub AAA() MsgBox "aaa" End Sub Sub BBB() MsgBox "bbb" End Sub The AAA and BBB subs are just stubs. You can put your macro code that does the real work there--or call your macros from them. ker_01 wrote: Thank you Dave- it looks like an add-in will be the best way to deploy my toolbar. I'll use code to install and load (and unload) the add-in from my actual workbook. The one piece I'm still struggling with is whether there is any way to keep the custom button icons that I've already developed. I edited my icons within the Excel icon editor, so I don't have them saved anywhere as a separate file. Is my only option to recreate each 16x16 (bitmap) icon in an image editing program, or is there any way to grab a button image (perhaps by VBA) and save that image to the worksheet so I can copy it into my new add-in? Thanks for any advice, Keith "Dave Peterson" wrote in message ... 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) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. ker_01 wrote: I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deploying custom toolbar and custom icons
Glad you got it working, well, close to the way you want <vbg.
ker_01 wrote: Thanks again Dave! I've adapted this code, and pasted my icons on a hidden sheet. Everything is working (almost) perfectly; the only downside is that the pasted icons don't seem to have a transparent background anymore, and the background gray used is a little different from the toolbar color. This is an internal project so I'm not worried, and if it bugs me too much I'll just figure out which color matches the toolbar grey and go backfill the icons manually. Thanks!! Keith "Dave Peterson" wrote in message ... Here's one that uses pictures on a worksheet -- nicely named! Option Explicit Public Const ToolBarName As String = "MyToolbarName" 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 Dim CapNames As Variant Dim TipText As Variant Dim PictNames As Variant Dim PictWks As Worksheet Call RemoveMenubar MacNames = Array("aaa", _ "bbb") CapNames = Array("AAA Caption", _ "BBB Caption") TipText = Array("AAA tip", _ "BBB tip") PictNames = Array("Pic1", "Pic2") Set PictWks = ThisWorkbook.Worksheets("Pictures") With Application.CommandBars.Add .Name = ToolBarName .Left = 200 .Top = 200 .Protection = msoBarNoProtection .Visible = True .Position = msoBarFloating For iCtr = LBound(MacNames) To UBound(MacNames) PictWks.Pictures(PictNames(iCtr)).Copy With .Controls.Add(Type:=msoControlButton) .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr) .Caption = CapNames(iCtr) .Style = msoButtonIconAndCaption .PasteFace .TooltipText = tip_text(iCtr) End With Next iCtr End With End Sub Sub AAA() MsgBox "aaa" End Sub Sub BBB() MsgBox "bbb" End Sub The AAA and BBB subs are just stubs. You can put your macro code that does the real work there--or call your macros from them. ker_01 wrote: Thank you Dave- it looks like an add-in will be the best way to deploy my toolbar. I'll use code to install and load (and unload) the add-in from my actual workbook. The one piece I'm still struggling with is whether there is any way to keep the custom button icons that I've already developed. I edited my icons within the Excel icon editor, so I don't have them saved anywhere as a separate file. Is my only option to recreate each 16x16 (bitmap) icon in an image editing program, or is there any way to grab a button image (perhaps by VBA) and save that image to the worksheet so I can copy it into my new add-in? Thanks for any advice, Keith "Dave Peterson" wrote in message ... 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) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. ker_01 wrote: I'm working on a custom toolbar for a workbook I'm building (plenty of buttons, including custom icons). We've decided to stick the workbook on the network and have other folks access it as well. What is the preferred method for ensuring the toolbar is available for all users when they open the workbook? It's been years since I had to deploy a custom toolbar, and that was in Word97 via a template- whereas this is in Excel2003, and it is an active workbook (not a template). I googled and found a lot about building a custom toolbar via code, but there were references to issues with custom icons, and I'm hoping there is a way to just keep the toolbar connected to the workbook without having to generate it via code for each user. Thanks for any advice, Keith -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What causes custom icons in the excel standard toolbar | Excel Discussion (Misc queries) | |||
Custom Toolbar Icons Ugly in Excel 2007 | Excel Programming | |||
Custom Toolbar Icons | Excel Discussion (Misc queries) | |||
Custom toolbar button icons | Excel Discussion (Misc queries) | |||
Custom menus and toolbar text and icons | Excel Programming |