Remove Userforms From ALL workbooks

Jaye7

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top