Remove Userforms From ALL workbooks

[Jaye7]

Can anyone help with a script to remove ALL userforms from ALL open workbooks.
I have a script that will remove a userform from the current workbook but not from ALL workbooks.

I have a template of userforms but I like userforms in each workbook that I use as I modify scripts to suit specific functions.

It sounds silly to do but it is something that I want so that I can reuse the file names for new userforms or userforms that I have updated.

Alternatively, a vba script to replace old userforms with updated userforms.

Thank you

 

[Leith Ross]

Hello Jaye7,

This macro will remove All UserForms in All open VBA Projects.

Sub RemoveAllUserForms()

  Dim VBComp As Object
  Dim VBProj As Object
  
    For Each VBProj In Application.VBE.VBProjects
      For Each VBComp In VBProj.VBComponents
        If VBComp.Type = 3 Then
           VBProj.VBComponents.Remove VBComp
        End If
      Next VBComp
    Next VBProj
    
End Sub

Sincerely,
Leith Ross

 

[Jaye7]

Hi Leith,

thanks for your quick reply, I have tried the macro and it bugs out on the following line.

I am using excel 2000 version

code:
For Each VBComp In VBProj.VBComponents

 

[Leith Ross]

Hello Jaye7,

Try adding a reference to the Microsoft Visual Basic for Applications Extensibility library.

Setting a Reference in VBA

1. Open your workbook and then press ALT+F11 to open the VBE.
2. Press ALT+T followed by the Enter key.
3. Scroll down the list until you find Microsoft Visual Basic for Applications x.x Object Library.
4. Press the Space bar to select it, and press Enter
5. Press CTRL+S to save the reference in the project

Sincerely,
Leith Ross

 

[Jaye7]

Leith

I have opened up the components section using Alt T however when I scroll down the list I can not find
Microsoft Visual Basic for Applications x.x Object Library. or anything close to it.
Sorry I haven't used the extensibility library before.

 

[Leith Ross]

Hello Jaye7,

The big question is why a standard library would be missing on your machine? The extensibility library ships with VBA. The x.x simply represents the major and minor versions of library. I assume you have VBA installed, yes?

 

[Jaye7]

Hi Leith,

Yes I have VBA Installed and I have found extensibility 5.3 but no x.x but it doesn't seem to save it even into 5.3 as when I press ctrl S it just goes to S in the references.

I don't want to hassle you, so if you want to unsubscribe it's fine, I will keep searching the forums, maybe I can find something to do the trick.

 

[mikerickson]

What code do you have that works with ThisWorkbook?

One approach would be to declare a variable oneWorkbook As Workbook.

Then replace ThisWorkbook with oneWorkbook throughout your code.

Finally, wrap the whole thing in a

For each oneWorkbook in Application.Workbooks
    Rem Your modified code
Next oneWorkbook