PDA

View Full Version : [SOLVED] Copy ThisWorkbook Code with Copy Sheet to New Book



IRish3538
07-24-2017, 11:49 AM
So i have this workbook and one of the functions makes several copies of one of the sheets (let's call them input forms) and saves them as new workbooks. The input forms have code in them from the original workbook which obviously extends to the new xlsm files. My problem is that i want to have a 'before save' event in the input forms (which is a workbook-level event) but only the worksheet-level code transfers to the newly saved files.

Any ideas on a workaround here?

Logit
07-24-2017, 12:21 PM
.
What does this mean ? Please explain :


only the worksheet-level code transfers

mdmackillop
07-24-2017, 12:29 PM
Add a routine to the copy code to write the text of Before Save to the new book's Workbook module

IRish3538
07-24-2017, 01:34 PM
how do I write code to write code to the workbook global event?

GTO
07-25-2017, 01:22 AM
By example:



Option Explicit

Sub example()
Const vbext_ct_StdModule = 1 'Standard module
Const vbext_ct_ClassModule = 2 'Class module
Const vbext_ct_MSForm = 3 'Microsoft Form
Const vbext_ct_ActiveXDesigner = 11 'ActiveX Designer
Const vbext_ct_Document = 100 'Document Module

Dim WB As Workbook
Dim VBComp As Object ' VBComponent
Dim VBCompThisWorkbook As Object ' VBComponent

'Create new WB and copy whatever sheet(s) to it.
Set WB = Workbooks.Add(xlWBATWorksheet)
Sheet1.Copy After:=WB.Worksheets(1)
Application.DisplayAlerts = False
'Delete the sheet initially created in the new WB
WB.Worksheets(1).Delete
Application.DisplayAlerts = True

For Each VBComp In WB.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
'Pick a component property that is different between the ThisWorkbook module
'and a worksheet's module.
If LCase$(VBComp.Properties(7).Name) = "author" Then
Set VBCompThisWorkbook = VBComp
Exit For
End If
End If
Next

If Not VBCompThisWorkbook Is Nothing Then

'Since we might not know whether the user has Option Explicit required, delete it if it's there.
VBCompThisWorkbook.CodeModule.DeleteLines 1, VBCompThisWorkbook.CodeModule.CountOfDeclarationLines
'Add our code
VBCompThisWorkbook.CodeModule.AddFromString _
"Option Explicit" & vbLf & vbLf & _
"Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)" & vbLf & vbLf & _
" If Not MsgBox(""Are you sure you want to save?"", vbYesNo Or vbQuestion, vbNullString) = vbYes Then" & vbLf & _
" Cancel = True" & vbLf & _
" MsgBox ""<Save> cancelled..."", vbInformation, vbNullString" & vbLf & _
" End If" & vbLf & vbLf & _
"End Sub"


End If


End Sub

IRish3538
07-27-2017, 10:56 AM
thanks! looks like I just got a new bag of tricks!

GTO
08-03-2017, 11:19 PM
Sorry, I missed seeing this. You are most welcome:)