CODEWITHSUNDEEP
excelvba
user2409958
user2409958

Reputation: 1

How can I get a macro to automatically carry forward from my template workbook to a newly created workbook regardless of the new filename?

I have a workbook that is the main template for our group. Within this template I have several tabs that I would like to incorporate macros on for hiding and unhiding of rows based on values. I setup this macro on one tab, tested it out and it worked. However, when I went through the process of what the users will be doing the macro did not carry forward to the new workbook. I do not want to waste time doing the macro on the other sheets if I am unable to have the macro carry forward. Can someone please help as my template is due today?

In case more info is needed on the process of the template....The template opens up with a User form for the individual to enter in specific information and then during the process of the uploading of an information feed it creates a new workbook.

Upvotes: 0

Views: 2424

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Write your template macro in a code module, then save and right-click the .bas module in the solution explorer, and export the module to a location you'll programmatically load it from later.

Then in another module, write the procedure that creates the workbook. Before saving the new workbook, load the exported module file into the workbook's VBProject.VBComponents collection, like this:

Dim targetWorkbook As Workbook
'...set up target workbook
targetWorkbook.VBProject.VBComponents.Import "module1.bas"
targetWorkbook.SaveAs "workbook.xls"

EDIT

If your macro code needs to listen to Excel's events (and/or worksheet events), you'll need to also add a class module where you're going to do exactly that. A code module will need to instantiate it for the class module's code to run. Create a new class module and call it something like "clsExcelApp":

Private WithEvents xlApp As Excel.Application
Option Explicit

Private Sub Class_Initialize()
    Set xlApp = Application
End Sub

Private Sub Class_Terminate()
    Set xlApp = Nothing
End Sub

Then you can write event handlers for xlApp, such as:

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
    'do something whenever a worksheet gets activated.
End Sub

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'do something before a workbook gets saved.
End Sub

The code module would only need to instantiate it, like this:

private App As New clsExcelApp
Option Explicit

The rest of the module could be macros and/or functions:

Public Sub Macro1()
    'do something
End Sub

Public Function Smurf(Range As Excel.Range) As Long
    'Smurf the Range and then smurf a Long
End Function

Then save your hard work and export all modules and import them programmatically into the workbook you want to "inject" the functionality into.

Note You should always restrict access to VBProject, but to execute the Excel.Workbook.VBProject.VBComponents.Import(String) method you'll need it enabled. Just remember to turn the security back on when you're done - better safe than sorry!

Upvotes: 1

Related Questions