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 fil
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 th
copy to your desired workbook. Close the temporary file and 'Kill' it
Record a macro for the synta
In earlier versions you can copy "Cells" from source to a new sheet in a ne
workbook (select the intersect of row/column headers. This will copy all th
shapes without code. In 2007 this is highly resource intensive, in earlie
versions it depends on the amount of contents and usedrange. If large, yo
can break down into smaller chunks
You say you want to leave controls (I assume Forms buttons) on the sheet t
call macros. If these are to call similarly named macros in the new workboo
you will need to edit links in the copied buttons
One other way is to remove all code from the copied sheet. Doable but onl
if access to VB Project is enabled, which some users may be wary of
Regards
Peter
<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 fil
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 th
copy to your desired workbook. Close the temporary file and 'Kill' it
Record a macro for the synta
In earlier versions you can copy "Cells" from source to a new sheet in a ne
workbook (select the intersect of row/column headers. This will copy all th
shapes without code. In 2007 this is highly resource intensive, in earlie
versions it depends on the amount of contents and usedrange. If large, yo
can break down into smaller chunks
You say you want to leave controls (I assume Forms buttons) on the sheet t
call macros. If these are to call similarly named macros in the new workboo
you will need to edit links in the copied buttons
One other way is to remove all code from the copied sheet. Doable but onl
if access to VB Project is enabled, which some users may be wary of
Regards
Peter
<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
Similar Threads
