Create/Delete/Reset module and userform by VBA

[SeniorNewbie]

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:

' 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:

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

 

[RoryA]

Why do you need to delete the actual userform, rather than just reusing it? You can alter its properties to whatever you want.

 

[SeniorNewbie]

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.

 

[RoryA]

In this case it's much easier just to kill the usf and redesign it

Not really, if it doesn't work.

You can't kill the old name.

 

[SeniorNewbie]

"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?

 

[SeniorNewbie]

btw: FYI my workaround updates the name of my USF with

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.

 

[Domenic]

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...

wb.Save

Hope this helps!

 

[SeniorNewbie]

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

 

[Domenic]

Don't know why, probably a design flaw. Maybe you should ask the engineers at Microsoft.

Anyway, I'm glad it helped.

Cheers!