Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Universal CommandButtons Accross Worksheets
I have a workbook with sheets for everyday of the month in it. I want to add
common command buttons that are visible and usable in every sheet. That way it will automate some routine actions. Right now I have six command buttons and 485 lines of code for Just One worksheet that is not yet populated with daily meta-data. My question is -- how do I "Share" the buttons and the code for the buttons accross all 30 sheets without having to copy&paste them and the code into every worksheet because if I do that then the size of the file grows and the reaction time of the spreadsheet is decreased? I've tried using "ThisWorkbook" and Modules but I still have to place the buttons into everyworks sheet and then configure every button like this... Private Sub DoThis_Click() ThisWorksheet.SetCustomBorders End Sub Any help would be very nice. Thanks in Advance, Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Universal CommandButtons Accross Worksheets
You can use
ViewToolbarscustomize Use Add to create your own Toolbar and add buttons to it that run your macro's See Excel help for more Or you can add them with code to the right click menu for example when you activate the workbook and delete them when you deactivate it http://www.rondebruin.nl/menuid.htm#Add See sub Add_Controls -- Regards Ron de Bruin http://www.rondebruin.nl "Rob" wrote in message ... I have a workbook with sheets for everyday of the month in it. I want to add common command buttons that are visible and usable in every sheet. That way it will automate some routine actions. Right now I have six command buttons and 485 lines of code for Just One worksheet that is not yet populated with daily meta-data. My question is -- how do I "Share" the buttons and the code for the buttons accross all 30 sheets without having to copy&paste them and the code into every worksheet because if I do that then the size of the file grows and the reaction time of the spreadsheet is decreased? I've tried using "ThisWorkbook" and Modules but I still have to place the buttons into everyworks sheet and then configure every button like this... Private Sub DoThis_Click() ThisWorksheet.SetCustomBorders End Sub Any help would be very nice. Thanks in Advance, Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Universal CommandButtons Accross Worksheets
Well, I tried it but now the problem is that when I generate a new month and
try to use the commands it automatically opens the original workbook. so if I archive it or move it the toolbar no longer works. How do I fix that? Thanks Much. Rob "Ron de Bruin" wrote: You can use ViewToolbarscustomize Use Add to create your own Toolbar and add buttons to it that run your macro's See Excel help for more Or you can add them with code to the right click menu for example when you activate the workbook and delete them when you deactivate it http://www.rondebruin.nl/menuid.htm#Add See sub Add_Controls -- Regards Ron de Bruin http://www.rondebruin.nl "Rob" wrote in message ... I have a workbook with sheets for everyday of the month in it. I want to add common command buttons that are visible and usable in every sheet. That way it will automate some routine actions. Right now I have six command buttons and 485 lines of code for Just One worksheet that is not yet populated with daily meta-data. My question is -- how do I "Share" the buttons and the code for the buttons accross all 30 sheets without having to copy&paste them and the code into every worksheet because if I do that then the size of the file grows and the reaction time of the spreadsheet is decreased? I've tried using "ThisWorkbook" and Modules but I still have to place the buttons into everyworks sheet and then configure every button like this... Private Sub DoThis_Click() ThisWorksheet.SetCustomBorders End Sub Any help would be very nice. Thanks in Advance, Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Universal CommandButtons Accross Worksheets
You can add the macro's to your personal.xls but creating the menu's with code is what I like
Have you try the cell menu example ? and run the code in this two events in the thisworkbook module Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub Or see http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://www.j-walk.com/ss/excel/tips/tip53.htm Creating Custom Menus (John Walkenbach) TIP http://www.erlandsendata.no/english/...oadcommandbars Ole P. Erlandsen's Web Site (Example workbooks) -- Regards Ron de Bruin http://www.rondebruin.nl "Rob" wrote in message ... Well, I tried it but now the problem is that when I generate a new month and try to use the commands it automatically opens the original workbook. so if I archive it or move it the toolbar no longer works. How do I fix that? Thanks Much. Rob "Ron de Bruin" wrote: You can use ViewToolbarscustomize Use Add to create your own Toolbar and add buttons to it that run your macro's See Excel help for more Or you can add them with code to the right click menu for example when you activate the workbook and delete them when you deactivate it http://www.rondebruin.nl/menuid.htm#Add See sub Add_Controls -- Regards Ron de Bruin http://www.rondebruin.nl "Rob" wrote in message ... I have a workbook with sheets for everyday of the month in it. I want to add common command buttons that are visible and usable in every sheet. That way it will automate some routine actions. Right now I have six command buttons and 485 lines of code for Just One worksheet that is not yet populated with daily meta-data. My question is -- how do I "Share" the buttons and the code for the buttons accross all 30 sheets without having to copy&paste them and the code into every worksheet because if I do that then the size of the file grows and the reaction time of the spreadsheet is decreased? I've tried using "ThisWorkbook" and Modules but I still have to place the buttons into everyworks sheet and then configure every button like this... Private Sub DoThis_Click() ThisWorksheet.SetCustomBorders End Sub Any help would be very nice. Thanks in Advance, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum accross worksheets using names | Excel Worksheet Functions | |||
VLookup accross multiple worksheets | Excel Worksheet Functions | |||
SumIf accross multiple worksheets | Excel Worksheet Functions | |||
Copying Sum formula accross to other worksheets | Excel Discussion (Misc queries) | |||
Making a macro universal to create multiple worksheets | Excel Programming |