Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
Hello -
I have a macro that copies a sheet out of "ThisWorkbook", pastes it into a new workbook, and then e-mails the new workbook to a recipient list. This works fine. My problem is, the "Sheet" object contains code - OnSelectionChange, AfterDoubleClick, AfterRightClick Events. When the sheet is copied, this code is also copied into the new book, so when the recipient opens and selects a cell, the SelectionChanged event fires, causing an error. Thought I could just throw in an error handler and avoid this, but it throws a "Compile Error" and opens the debugger every time, which is confusing the hell out of the recipients. Is there a way to copy the sheet without the code, or programatically erase the code? Thanks for any help - Sean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
Hi Sean
My SendMail add in have this option http://www.rondebruin.nl/mail/add-in.htm Or look here http://www.cpearson.com/excel/vbe.htm Another option is to copy all cells to a new worksheet and send that sheet -- Regards Ron de Bruin http://www.rondebruin.nl "What-A-Tool" wrote in message ... Hello - I have a macro that copies a sheet out of "ThisWorkbook", pastes it into a new workbook, and then e-mails the new workbook to a recipient list. This works fine. My problem is, the "Sheet" object contains code - OnSelectionChange, AfterDoubleClick, AfterRightClick Events. When the sheet is copied, this code is also copied into the new book, so when the recipient opens and selects a cell, the SelectionChanged event fires, causing an error. Thought I could just throw in an error handler and avoid this, but it throws a "Compile Error" and opens the debugger every time, which is confusing the hell out of the recipients. Is there a way to copy the sheet without the code, or programatically erase the code? Thanks for any help - Sean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
"Ron de Bruin" wrote in message
... Hi Sean My SendMail add in have this option http://www.rondebruin.nl/mail/add-in.htm Or look here http://www.cpearson.com/excel/vbe.htm Thanks - haven't had a chance to dig too deep, but the code samples look like exactly what I need |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
"Ron de Bruin" wrote in message
... Hi Sean My SendMail add in have this option http://www.rondebruin.nl/mail/add-in.htm Or look here http://www.cpearson.com/excel/vbe.htm Another option is to copy all cells to a new worksheet and send that sheet Decided to give some of the code from the CPearson site you suggested a try. The following, slightly modified code, ran without error, but failed to delete any code. I am trying to delete code from a Sheet module (in another book) - at first I thought maybe I couldn't deleete from a sheet, so I imported another code module and tried - didn't delete anything from any modules. ThisWorkbook is running the code to delete code from another workbook - is that possible? Public Sub DeleteAllCodeInModule(ByVal OtherBookName As String, _ ByVal Sheet2DelCodeFrom As Integer) Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Dim OtherBook As Workbook Set OtherBook = Workbooks(OtherBookName) Set VBCodeMod = OtherBook.VBProject.VBComponents(Sheet2DelCodeFrom ).CodeModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With End Sub Public Sub DeleteAllVBA(ByVal OtherBookName As String) Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents Dim OtherBook As Workbook Set OtherBook = Workbooks(OtherBookName) Set VBComps = OtherBook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
Decided to give some of the code from the CPearson site you suggested a
try. The following, slightly modified code, ran without error, but failed to delete any code. I am trying to delete code from a Sheet module (in another book) - at first I thought maybe I couldn't deleete from a sheet, so I imported another code module and tried - didn't delete anything from any modules. ThisWorkbook is running the code to delete code from another workbook - is that possible? The code does work on ThisWorbook - was able to delete code from a book running the code. Is it possible to delete code from another book? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
The following, slightly modified code, ran without error, but failed to
delete any code. No code ? -- Regards Ron de Bruin http://www.rondebruin.nl "What-A-Tool" wrote in message ... Decided to give some of the code from the CPearson site you suggested a try. The following, slightly modified code, ran without error, but failed to delete any code. I am trying to delete code from a Sheet module (in another book) - at first I thought maybe I couldn't deleete from a sheet, so I imported another code module and tried - didn't delete anything from any modules. ThisWorkbook is running the code to delete code from another workbook - is that possible? The code does work on ThisWorbook - was able to delete code from a book running the code. Is it possible to delete code from another book? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
"Ron de Bruin" wrote in message ... The following, slightly modified code, ran without error, but failed to delete any code. No code ? -- Regards Ron de Bruin http://www.rondebruin.nl No - it deleted nothing from the other workbook. I changed the code back to the original references to "ThisWorkBook" and was able to delete code from "ThisWorkBook" - but when I changed it to reference another book, it ran cleanly, but deleted nothing. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
Hi What-A-Tool
This is working OK for the activeworkbook No reference needed in the example Public Sub DeleteAllVBA() Dim VBComp As Object Dim VBComps As Object Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case 1, 3, _ 2 VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "What-A-Tool" wrote in message ... "Ron de Bruin" wrote in message ... The following, slightly modified code, ran without error, but failed to delete any code. No code ? -- Regards Ron de Bruin http://www.rondebruin.nl No - it deleted nothing from the other workbook. I changed the code back to the original references to "ThisWorkBook" and was able to delete code from "ThisWorkBook" - but when I changed it to reference another book, it ran cleanly, but deleted nothing. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet without code?
"Ron de Bruin" wrote in message ... Hi What-A-Tool This is working OK for the activeworkbook No reference needed in the example Public Sub DeleteAllVBA() Dim VBComp As Object Dim VBComps As Object Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case 1, 3, _ 2 VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub Thanks Ron - I'll give it a go. Didn't think to try the ActiveBook thing - sometimes I have a way of seeing the difficult way of doing things before the obvious. Thanks again for the help - Sean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code to copy sheet format to another sheet | Excel Discussion (Misc queries) | |||
what is the vb code to copy rows from one sheet to another? | Excel Discussion (Misc queries) | |||
How do i copy a active sheet to a new sheet with code and everything | Excel Programming | |||
Copy sheet without Code | Excel Programming | |||
Copy Sheet w/o VBA code | Excel Programming |