Excel vba add code to sheet module programmatically

by

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.

enter image description here

Hit the Stop Icon and select the tools menu and “References”

enter image description here

enter image description here

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

enter image description here

Run the code again and it should work.

More Related Contents:

  1. How do I get values from a hidden worksheet using VBA [closed]
  2. How to copy cells that contain certain text to another worksheet [closed]
  3. VBA Macro for copying and pasting columns on same excel sheet
  4. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  5. Levenshtein Distance in VBA [closed]
  6. Unprotect VBProject from VB code
  7. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  8. How do I base64 encode a string efficiently using Excel VBA?
  9. Assign event handlers to controls on user form created dynamically in VBA
  10. Programmatic Access To Visual Basic Project Is Not Trusted
  11. Can I make this macro more efficient or faster?
  12. Conditional Formatting using Excel VBA code
  13. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  14. How to get the background color from a Conditional Formatting in Excel using VBA
  15. Best workaround for VBA Transpose array length limit?
  16. How to Lock the data in a cell in excel using vba
  17. Open a workbook using FileDialog and manipulate it in Excel VBA
  18. How to use Events with Option Button Controls on Userform [duplicate]
  19. How do I use variables to set properties in VBA (Excel)
  20. VBA: Else without If Error
  21. Workbooks.Open Method in VBA
  22. Windows().Activate works on every computer except one
  23. How to highlight a cell using the hex color value within the cell?
  24. Convert RTF (Rich Text Format) code into plain text in Excel
  25. lastrow and excel table.
  26. Open a workbook from VBA and disable Workbook_Open() code?
  27. Creating form programmatically in the module using vba
  28. How to filter listbox values based on a Textbox value
  29. Find and FindNext for Excel VBA
  30. VBA inheritance, analog of super

Leave a Comment