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

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

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