Copy ThisWorkbook Code with Copy Sheet to New Book

[IRish3538]

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]

.
What does this mean ? Please explain :

only the worksheet-level code transfers

[mdmackillop]

Add a routine to the copy code to write the text of Before Save to the new book's Workbook module

[IRish3538]

how do I write code to write code to the workbook global event?

[GTO]

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]

thanks! looks like I just got a new bag of tricks!

[GTO]

Sorry, I missed seeing this. You are most welcome