Add Multiple VBA codes progammaticly

[dchaney]

Hello All,

I am having some issues. I have been able to create code that allows me to add VBA to the "ThisWorkbook" module a new workbook when I create it (Open, BeforeClose, SheetCalculate & SheetSelectionChange). The issue I am running into is how to add multiple procedures to a new module that I create. I think the issue I am having, is not knowing how to call the (General) object in the module (like Open, or BeforeClose on the top code).

Here is the code that works for me:

Dim lnStartLine As Long
Dim stCode As String, newModule
    
    stCode = "" & vbCrLf
    stCode = stCode & "Call SetTimer"
    '****************************************************************************************
    'ADD NEW MACRO WITH ABOVE TEXT TO NEW WORKBOOK                                          *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        lnStartLine = .CreateEventProc("Open", "Workbook") + 1
        .InsertLines lnStartLine, stCode
    End With
'********************************************************************************************
      
    stCode = "" & vbCrLf
    stCode = stCode & "Call StopTimer"
    '****************************************************************************************
    'ADD NEW MACRO WITH ABOVE TEXT TO NEW WORKBOOK                                          *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        lnStartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
        .InsertLines lnStartLine, stCode
    End With
'********************************************************************************************


    stCode = "" & vbCrLf
    stCode = stCode & "Call StopTimer" & vbCrLf
    stCode = stCode & "Call SetTimer"
    '****************************************************************************************
    'ADD NEW MACRO WITH ABOVE TEXT TO NEW WORKBOOK                                          *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        lnStartLine = .CreateEventProc("SheetCalculate", "Workbook") + 1
        .InsertLines lnStartLine, stCode
    End With
'********************************************************************************************
       
    stCode = "" & vbCrLf
    stCode = stCode & "Call StopTimer" & vbCrLf
    stCode = stCode & "Call SetTimer"
    '****************************************************************************************
    'ADD NEW MACRO WITH ABOVE TEXT TO NEW WORKBOOK                                          *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        lnStartLine = .CreateEventProc("SheetSelectionChange", "Workbook") + 1
        .InsertLines lnStartLine, stCode
    End With

And here is the code I am having issues with:

    '****************************************************************************************    
    'CREATE A NEW MODULE1 IN THE NEW WORKBOOK                                               *
    '****************************************************************************************
    Set newModule = ActiveWorkbook.VBProject.VBComponents.Add(1)
'********************************************************************************************
    
    stCode = "Public DownTime As Date" & vbCrLf & vbCrLf


    stCode = stCode & "Sub SetTimer()" & vbCrLf & vbCrLf
    stCode = stCode & "DownTime = Now + TimeValue(""00:10:00"")" & vbCrLf
    stCode = stCode & "Application.OnTime EarliestTime:=DownTime, Procedure:" & _
    stCode = stCode & "=""ShutDown"", Schedule:=True" & vbCrLf & vbCrLf
    stCode = stCode & "End Sub" & vbCrLf


    stCode = stCode & "Sub StopTimer()" & vbCrLf & vbCrLf
    stCode = stCode & "On Error Resume Next" & vbCrLf
    stCode = stCode & "Application.OnTime EarliestTime:=DownTime, Procedure:" & _
    stCode = stCode & "=""ShutDown"", Schedule:=False" & vbCrLf & vbCrLf
    stCode = stCode & "End Sub" & vbCrLf & vbCrLf


    stCode = stCode & "Sub ShutDown()" & vbCrLf & vbCrLf
    stCode = stCode & "ThisWorkbook.Close SaveChanges:=True" & vbCrLf & vbCrLf
    stCode = stCode & "End Sub" & vbCrLf & vbCrLf
    '****************************************************************************************
    'ADD THE ABOVE TEXT TO THE "MODULE1" MODULE                                             *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
        .InsertLines .CountOfLines + 1, stCode
    End With
'********************************************************************************************

Any assistance is appreciated...

 

[dchaney]

I ended up fixing it, the error was in the following code:

[COLOR=#333333]    stCode = stCode & "Application.OnTime EarliestTime:=DownTime, Procedure:" & _
[/COLOR][COLOR=#333333]    stCode = stCode & "=""ShutDown"", Schedule:=False" & vbCrLf & vbCrLf[/COLOR]

I had to add the two lines together

[COLOR=#574123]stCode = stCode & "    Application.OnTime EarliestTime:=DownTime, Procedure:=""ShutDown"", Schedule:=False" & vbCrLf & vbCrLf[/COLOR]

here is the code I used

    '****************************************************************************************    'CREATE A NEW MODULE1 IN THE NEW WORKBOOK                                               *
    '****************************************************************************************
    Set newModule = ActiveWorkbook.VBProject.VBComponents.Add(1)
    ActiveWorkbook.VBProject.VBComponents("Module1").Name = "SetTimerMod"
'********************************************************************************************
    
    stCode = "Public DownTime As Date" & vbCrLf & vbCrLf


    stCode = stCode & "Sub SetTimer()" & vbCrLf & vbCrLf
    stCode = stCode & "    DownTime = Now + TimeValue(""00:10:00"")" & vbCrLf
    stCode = stCode & "    Application.OnTime EarliestTime:=DownTime, Procedure:=""ShutDown"", Schedule:=True" & vbCrLf & vbCrLf
    stCode = stCode & "End Sub" & vbCrLf
    '****************************************************************************************
    'ADD THE ABOVE TEXT TO THE "MODULE1" MODULE                                             *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("SetTimerMod").CodeModule
        .InsertLines .CountOfLines + 1, stCode
    End With


    '****************************************************************************************
    'CREATE A NEW MODULE1 IN THE NEW WORKBOOK                                               *
    '****************************************************************************************
    Set newModule = ActiveWorkbook.VBProject.VBComponents.Add(1)
    ActiveWorkbook.VBProject.VBComponents("Module1").Name = "StopTimerMod"
'********************************************************************************************


    stCode = "Sub StopTimer()" & vbCrLf & vbCrLf
    stCode = stCode & "On Error Resume Next" & vbCrLf
    stCode = stCode & "    Application.OnTime EarliestTime:=DownTime, Procedure:=""ShutDown"", Schedule:=False" & vbCrLf & vbCrLf
    stCode = stCode & "End Sub" & vbCrLf & vbCrLf
    '****************************************************************************************
    'ADD THE ABOVE TEXT TO THE "MODULE1" MODULE                                             *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("StopTimerMod").CodeModule
        .InsertLines .CountOfLines + 1, stCode
    End With
    
    '****************************************************************************************
    'CREATE A NEW MODULE1 IN THE NEW WORKBOOK                                               *
    '****************************************************************************************
    Set newModule = ActiveWorkbook.VBProject.VBComponents.Add(1)
    ActiveWorkbook.VBProject.VBComponents("Module1").Name = "ShutDownMod"
'********************************************************************************************


    stCode = "Sub ShutDown()" & vbCrLf & vbCrLf
    stCode = stCode & "    ThisWorkbook.Close SaveChanges:=True" & vbCrLf & vbCrLf
    stCode = stCode & "End Sub" & vbCrLf & vbCrLf
    '****************************************************************************************
    'ADD THE ABOVE TEXT TO THE "MODULE1" MODULE                                             *
    '****************************************************************************************
    With ActiveWorkbook.VBProject.VBComponents("ShutDownMod").CodeModule
        .InsertLines .CountOfLines + 1, stCode
    End With
'********************************************************************************************