Remove Userforms From ALL workbooks

Jaye7

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

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. 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,058
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,874
Office Version
  1. 2010
  2. 2007
Platform
  1. 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,058

ADVERTISEMENT

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,874
Office Version
  1. 2010
  2. 2007
Platform
  1. 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,058
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
23,901
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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
Top