CODEWITHSUNDEEP
excelvbauserform
Kenny
Kenny

Reputation: 353

Delete a UserForm and add a New UserForm in Excel VBA

What I would like to do is Delete a Userform and replace it with an updated Userform using VBA for Excel.

I exported the original Userform, made my changes, and now would like to replace the original with the new Userform. Both Userforms have the same names. I'm hoping, by keeping the UserForm names the same the button that activates the Userform won't be damaged.

I have am going to have the VBA loop through all the workbooks and do this ~3200 times.

Sub Macro2() 
'''delete and replace a UserForm

'Dim some stuff
Application.ScreenUpdating = False

    Dim SourceRow As Long
    Dim EditRow As Long
    Dim sFile As String
    Dim wb As Workbook
    Dim FileName1 As String
    Dim FileName2 As String
    Dim wksSource As Worksheet
    Const scWkbSourceName As String = "theFILE.xlsm"

    Set wkbSource = Workbooks(scWkbSourceName)
    Set wksSource = wkbSource.Sheets("Sheet1")
    Const wsOriginalBook As String = "theFILE.xlsm"
    Const sPath As String = "C:\examplefolder\" 'this is PATH

    SourceRow = 5

''ENSURE SELECT SOURCE SHEET
    Sheets("Sheet1").Select

Do While Cells(SourceRow, "C").Value <> ""

    FileName1 = wksSource.Range("A" & SourceRow).Value
    FileName2 = wksSource.Range("L" & SourceRow).Value
    EditRow = 2
    sFile = sPath & FileName1 & "\" & FileName2 & ".xlsm"

    Set wb = Workbooks.Open(sFile)

''DIM OPEN WORKBOOK
    Dim AWorkbook As Workbook
    Set AWorkbook = ActiveWorkbook

        With AWorkbook.VBProject.VBComponents
            .Remove.Item ("NewJobEvent")
        End With

''CLOSE WORKBOOK W/O BEFORE SAVE
    Application.EnableEvents = False
    ActiveWorkbook.Save
    Application.EnableEvents = True
    ActiveWorkbook.Close

    Windows("theFILE.xlsm").Activate
    Sheets("Sheet1").Select

SourceRow = SourceRow + 1 ' Move down 1 row for source sheet

Loop
End Sub

Any and all help and pointers are greatly appreciated ahead of time.

Upvotes: 0

Views: 5220

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

From: http://www.cpearson.com/excel/vbe.aspx

Sub DeleteModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    VBProj.VBComponents.Remove VBComp
End Sub

Remove take a module reference, not a string

To import a module from a file:

ActiveWorkbook.VBProject.VBComponents.Import FullPathHere

Upvotes: 2

Related Questions