Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Macros with Sheets to New Workbook
Thanks for taking the time to read my question.
I have a workbook that has a lot of sheets in it. I'd like to be able to make a copy of a sheet and place it in a new workbook, then e-mail the new workbook. I want to send just the one sheet becuase each sheet is for a different customer, and the information is sensitive. My problem is... I have macros on each sheet. When I copy a sheet into a new workbook, the macro comes with it, but the button on the sheet still points to the macro in the original workbook. You have to right mouse click "Assign Macro" and choose the macro in the current workbook. I don't want the clients to have to do this. Is there a property setting that tells Excel to use the macro in the new workbook instead of the old workbook. Thanks again for the help. Brad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Macros with Sheets to New Workbook
The answer is:
Create a button using the Control Toolbox instead of the Forms toolbar. Doing this will embed the code to the button, not the workbook, and the macro will move with the sheet. Brad "Brad" wrote: Thanks for taking the time to read my question. I have a workbook that has a lot of sheets in it. I'd like to be able to make a copy of a sheet and place it in a new workbook, then e-mail the new workbook. I want to send just the one sheet becuase each sheet is for a different customer, and the information is sensitive. My problem is... I have macros on each sheet. When I copy a sheet into a new workbook, the macro comes with it, but the button on the sheet still points to the macro in the original workbook. You have to right mouse click "Assign Macro" and choose the macro in the current workbook. I don't want the clients to have to do this. Is there a property setting that tells Excel to use the macro in the new workbook instead of the old workbook. Thanks again for the help. Brad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Macros with Sheets to New Workbook
A workaround might be
activesheet.copy for each btn in activesheet.buttons btn.OnAction = application.Substitute(btn.OnAction, _ oldfilename,newfilename) Next Is the code for the buttons in the sheet module? -- Regards, Tom Ogilvy "Brad" wrote in message ... Thanks for taking the time to read my question. I have a workbook that has a lot of sheets in it. I'd like to be able to make a copy of a sheet and place it in a new workbook, then e-mail the new workbook. I want to send just the one sheet becuase each sheet is for a different customer, and the information is sensitive. My problem is... I have macros on each sheet. When I copy a sheet into a new workbook, the macro comes with it, but the button on the sheet still points to the macro in the original workbook. You have to right mouse click "Assign Macro" and choose the macro in the current workbook. I don't want the clients to have to do this. Is there a property setting that tells Excel to use the macro in the new workbook instead of the old workbook. Thanks again for the help. Brad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
moving data between workbook sheets | Excel Discussion (Misc queries) | |||
Moving Sheets within a workbook | Excel Worksheet Functions | |||
Macros for Protect/Unprotect all sheets in a workbook | Excel Discussion (Misc queries) | |||
Moving a variable number of Sheets to another Workbook | Excel Programming |