Excel vba add code to sheet module programmatically
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
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.