Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable worksheet macros when copying a worksheet in VBA
I am using the worksheet.copy method to copy a worksheet intact. The source worksheet has a few event macros that I would like to disable in the destination worksheet.
I considered just copying the contents of the source to a new worksheet. However, the source worksheet contains some controls I would like to keep in the destination sheet. These controls call to module subroutines outside the worksheet. I can see two possible solutions, but I do not know how to execute any of them: 1) Disable all worksheet VBA code for the destination worksheet 2) Copy the contents of the source worksheet, and then copy only the buttons I want to use. Can someone point me to the method or function I can use for either strategy? Thanks, Craig. Submitted via EggHeadCafe - Software Developer Portal of Choice ..NET Abstract Factory http://www.eggheadcafe.com/tutorials...t-factory.aspx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable worksheet macros when copying a worksheet in VBA
If you have Excel 2007, use the worksheet.copy method to create a new file
with the single sheet. Save it as an xlsx (you'll need to disable alerts). Close the file and reopen it. Now it will have lost all code, repeat the copy to your desired workbook. Close the temporary file and 'Kill' it. Record a macro for the syntax In earlier versions you can copy "Cells" from source to a new sheet in a new workbook (select the intersect of row/column headers. This will copy all the shapes without code. In 2007 this is highly resource intensive, in earlier versions it depends on the amount of contents and usedrange. If large, you can break down into smaller chunks. You say you want to leave controls (I assume Forms buttons) on the sheet to call macros. If these are to call similarly named macros in the new workbook you'll need to edit links in the copied buttons. One other way is to remove all code from the copied sheet. Doable but only if access to VB Project is enabled, which some users may be wary of. Regards, Peter T <Craig Remillard wrote in message ... I am using the worksheet.copy method to copy a worksheet intact. The source worksheet has a few event macros that I would like to disable in the destination worksheet. I considered just copying the contents of the source to a new worksheet. However, the source worksheet contains some controls I would like to keep in the destination sheet. These controls call to module subroutines outside the worksheet. I can see two possible solutions, but I do not know how to execute any of them: 1) Disable all worksheet VBA code for the destination worksheet 2) Copy the contents of the source worksheet, and then copy only the buttons I want to use. Can someone point me to the method or function I can use for either strategy? Thanks, Craig. Submitted via EggHeadCafe - Software Developer Portal of Choice .NET Abstract Factory http://www.eggheadcafe.com/tutorials...t-factory.aspx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution used
Peter,
Thank you for the reply. Here is what I ended up doing: 'Delete unneeded buttons For Each c In Worksheets(PShtName).Shapes If c.Type = 8 Then Select Case c.AlternativeText Case "HR Only", "XR Only", "SR Only", "All", "Pad Schedule" c.Delete Case Else End Select End If Next c The code loops through all the shapes on the page, and finds the buttons that need to go by the AlternativeText property. I handled the different sheet names by using ActiveSheet to set my worksheet name. This works since, when you click the button, you have to be in the right sheet. Peter T wrote: If you have Excel 2007, use the worksheet. 24-Dec-09 If you have Excel 2007, use the worksheet.copy method to create a new file with the single sheet. Save it as an xlsx (you will need to disable alerts). Close the file and reopen it. Now it will have lost all code, repeat the copy to your desired workbook. Close the temporary file and 'Kill' it. Record a macro for the syntax In earlier versions you can copy "Cells" from source to a new sheet in a new workbook (select the intersect of row/column headers. This will copy all the shapes without code. In 2007 this is highly resource intensive, in earlier versions it depends on the amount of contents and usedrange. If large, you can break down into smaller chunks. You say you want to leave controls (I assume Forms buttons) on the sheet to call macros. If these are to call similarly named macros in the new workbook you will need to edit links in the copied buttons. One other way is to remove all code from the copied sheet. Doable but only if access to VB Project is enabled, which some users may be wary of. Regards, Peter T <Craig Remillard wrote in message Previous Posts In This Thread: On Wednesday, December 23, 2009 9:42 PM Craig Remillard wrote: Disable worksheet macros when copying a worksheet in VBA I am using the worksheet.copy method to copy a worksheet intact. The source worksheet has a few event macros that I would like to disable in the destination worksheet. I considered just copying the contents of the source to a new worksheet. However, the source worksheet contains some controls I would like to keep in the destination sheet. These controls call to module subroutines outside the worksheet. I can see two possible solutions, but I do not know how to execute any of them: 1) Disable all worksheet VBA code for the destination worksheet 2) Copy the contents of the source worksheet, and then copy only the buttons I want to use. Can someone point me to the method or function I can use for either strategy? Thanks, Craig. On Thursday, December 24, 2009 5:55 AM Peter T wrote: If you have Excel 2007, use the worksheet. If you have Excel 2007, use the worksheet.copy method to create a new file with the single sheet. Save it as an xlsx (you will need to disable alerts). Close the file and reopen it. Now it will have lost all code, repeat the copy to your desired workbook. Close the temporary file and 'Kill' it. Record a macro for the syntax In earlier versions you can copy "Cells" from source to a new sheet in a new workbook (select the intersect of row/column headers. This will copy all the shapes without code. In 2007 this is highly resource intensive, in earlier versions it depends on the amount of contents and usedrange. If large, you can break down into smaller chunks. You say you want to leave controls (I assume Forms buttons) on the sheet to call macros. If these are to call similarly named macros in the new workbook you will need to edit links in the copied buttons. One other way is to remove all code from the copied sheet. Doable but only if access to VB Project is enabled, which some users may be wary of. Regards, Peter T <Craig Remillard wrote in message Submitted via EggHeadCafe - Software Developer Portal of Choice Code Recommendations For COM+ VB Developers http://www.eggheadcafe.com/tutorials...tions-for.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying worksheet including graphs to new worksheet | Excel Discussion (Misc queries) | |||
disable macros in a worksheet | Excel Programming | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions | |||
Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming |