Excel vba add code to sheet module programmatically

Question

How to put the programmatically generated workbook an event code similar to below:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextTarget As Range

    Set nextTarget = Range(Selection.Address) 'store the next range the user selects

    Target.Columns.Select 'autofit requires columns to be selected
    Target.Columns.AutoFit

    nextTarget.Select
End Sub

So you're basically trying to create a workbook and then copy code to the freshly created working? — SilentRevolution
– SilentRevolution, Commented Jan 17, 2016 at 11:04
Options: use a template with the code in it, use the vba ide interface to programmatically add the code, or use an application level event in another workbook to run the code — chris neilsen
– chris neilsen, Commented Jan 17, 2016 at 11:10

Davesexcel · Accepted Answer · 2016-01-17 12:15:29Z

29

Use this to add a workbook and place a worksheet change event into the Sheet1 module.

Sub AddSht_AddCode()
    Dim wb As Workbook
    Dim xPro As VBIDE.VBProject
    Dim xCom As VBIDE.VBComponent
    Dim xMod As VBIDE.CodeModule
    Dim xLine As Long

    Set wb = Workbooks.Add

    With wb
        Set xPro = .VBProject
        Set xCom = xPro.VBComponents("Sheet1")
        Set xMod = xCom.CodeModule

        With xMod
            xLine = .CreateEventProc("Change", "Worksheet")
            xLine = xLine + 1
            .InsertLines xLine, "  Cells.Columns.AutoFit"
        End With
    End With

End Sub

When you 1st run the code you may get an error.

Hit the Stop Icon and select the tools menu and "References"

Then find "Microsoft Visual Basic for Applications Extensibility 5.3 library" and check it.

Run the code again and it should work.

answered Jan 17, 2016 at 12:15

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Doug Glancy Over a year ago

Nice one. I never noticed you could create an event procedure in the VBIDE like that.

Kelaref Over a year ago

is it possible to apply late-binding to above code? I got the error ActiveX component can't create object.

hornetbzz Over a year ago

Great. This needs to be completed by cpearson doc, which needs itself to be adjusted to the VBA and windows version (32/64 bits) See mdsn and this SO question

spinjector Over a year ago

I recommend against using CreateEventProc. I've found it causes the VBA Editor to unceremoniously open up in front of the user (or me). I just tested again to be certain. Instead, use InsertLines like this: InsertLines xLine, "Private Sub MyButton_Click()", followed by the procedure code, and an End Sub. From anything I've been able to determine, CreateEventProc doesn't do anything special that can't be done with InsertLines, except perhaps it simply glues the two parameters together with an underscore to get a procedure name in the format "ObjectName_EventName".

Majora Over a year ago

Is there a way to change the With property to insert the code into a specified pre-existing module? For example, say we want the code added by .InsertLines to be inserted into Line 5 in Module4?

Collectives™ on Stack Overflow

Excel vba add code to sheet module programmatically

1 Answer 1

When you 1st run the code you may get an error.

Hit the Stop Icon and select the tools menu and "References"

Then find "Microsoft Visual Basic for Applications Extensibility 5.3 library" and check it.

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

When you 1st run the code you may get an error.

Hit the Stop Icon and select the tools menu and "References"

Then find "Microsoft Visual Basic for Applications Extensibility 5.3 library" and check it.

5 Comments

Linked

Related