Create/Delete/Reset module and userform by VBA

SeniorNewbie

Board Regular
Joined
Jul 9, 2023
Messages
71
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
  2. MacOS
Hi out there,
my project is about creating userforms based on the values in a worksheet. People planning their USFs in advance or work with templates can accelerate the setup in a smart way. The code to create this both items is:
VBA Code:
' the objects are declared as VBComponent

Set mdl = wb.VBProject.VBComponents.Add(vbext_ct_StdModule)
    With mdl
        .Properties("Name") = "mdl_DynamicVBA"
   End With
Set USF = wb.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With USF
        .Properties("Name") = "usf_DynamicVBA"  
    End with
That works very fine. Of course I need to check the layout of the userform and to redesign it, so it's necessary to delete a former version, what's easily done by this code:
Code:
Sub ResetComponents()
Dim VBComps As VBComponents, VBComp As VBComponent
Set VBComps = wb.VBProject.VBComponents
    For Each VBComp In VBComps
        If Mid(VBComp.Name, 4, 1) = "_" Or Left(VBComp.Name, 4) = "User" Or Left(VBComp.Name, 5) = "Modul" Then
            Set VBComp = VBComps(VBComp.Name)
            VBComps.Remove VBComp
            Set VBComp = Nothing
        End If
    Next

End Sub

My question/problem however: while the code module works perfect the userform runs into an error because somewhere the first userform's name remains in the editor. No idea where.

Anybody out there who knows why usf and mdl react in different way - or how to kill the usf parameters in VBE?

THX a lot and have a wonderful day!

Senior Newbie
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Why do you need to delete the actual userform, rather than just reusing it? You can alter its properties to whatever you want.
 
Upvote 0
Hi Rory!
thx for the question. My system is table based to avoid that any user has to use the VBE. In this case it's much easier just to kill the usf and redesign it.

I found a workaround already by naming the next ufs with an index. But I want to learn where to kill the former usf name.
 
Upvote 0
"you can't..." Ok - a statement making me very sad ;) But why does it work with module so easy, although I'm using the same name again? What's the difference?
 
Upvote 0
btw: FYI my workaround updates the name of my USF with
VBA Code:
Dim lgTime as Long
lgTime = Abs(Timer)
USF.Properties("Name") = "usf_DynamicVBA_" & lgTime
what forces me anyway to modify the USF#s name in an extra routine.
 
Last edited:
Upvote 0
Another possible workaround might be to save the workbook after the components have been removed. So, basically, add the following line at the end of ResetComponents...

VBA Code:
wb.Save

Hope this helps!
 
Upvote 1
Solution
Hi Domenic,
sometimes I'm in the mood to commit suicide about my stupidness! Your solution is super easy - AND IT HELPS! and I should have been able to find it by myself.
THANK YOU VERY MUCH

But - why there's the difference mdl/usf

cu Senior Newbie
 
Upvote 0
Don't know why, probably a design flaw. Maybe you should ask the engineers at Microsoft. ;)

Anyway, I'm glad it helped.

Cheers!
 
Upvote 1

Forum statistics

Threads
1,223,375
Messages
6,171,734
Members
452,419
Latest member
mapa

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