Remove Userforms From ALL workbooks

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,871
Office Version
2010, 2007
Platform
Windows
Hello Jaye7,

This macro will remove All UserForms in All open VBA Projects.
Code:
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

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,871
Office Version
2010, 2007
Platform
Windows
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

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,871
Office Version
2010, 2007
Platform
Windows
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

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,814
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
Code:
For each oneWorkbook in Application.Workbooks
    Rem Your modified code
Next oneWorkbook
 

Forum statistics

Threads
1,082,549
Messages
5,366,237
Members
400,881
Latest member
aforte

Some videos you may like

This Week's Hot Topics

Top