Creating a Userform programmatically Error.

Herakles

Well-known Member
Joined
Jul 5, 2020
Messages
927
Office Version
  1. 365
Platform
  1. Windows
I'm creating a Userform programmatically which is fine but then I want to replace it based upon a different Specification.

I delete the Userform first and then create a new Userform but when I come to name it using the name of the Userform that I have just deleted it gives me an error
on this line : .Name = strName

Run-time error 75
Could not find the specified object.

Do I have to clear the deleted Userform from memory or something?

VBA Code:
Option Explicit

Private Sub subCreateuserform()
Dim MyUserForm As VBComponent
Dim strName As String

    ActiveWorkbook.Save
    
    Call subDeleteForm("frmMyForm")
    
    strName = "frmMyForm"

    Set MyUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
        
    With MyUserForm
        .Properties("Height") = 377
        .Properties("Width") = 260
        .Name = strName
        .Properties("Caption") = "My Form"
    End With
    
End Sub

Public Sub subDeleteForm(strUserform As String)
Dim VBComps As VBIDE.VBComponents
Dim VBComp As VBIDE.VBComponent

    Set VBComps = ActiveWorkbook.VBProject.VBComponents
    
    On Error Resume Next
        Set VBComp = VBComps(strUserform)
        VBComps.Remove VBComp
    On Error GoTo 0
    
    Set VBComps = Nothing
    
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I'm creating a Userform programmatically which is fine but then I want to replace it based upon a different Specification.
Why? Much simpler to just alter the controls on the form than creating a whole new form.
 
Upvote 0
Why? Much simpler to just alter the controls on the form than creating a whole new form.
But where's the fun in that? ;)

@Herakles your code works perfectly fine for me. My suggestion would be to pause between deleting the Userform and creating a new one with the same name as that which you've just deleted. I suspect that may be the point that @NdNoviceHlp is getting at with the DoEvents suggestion. If it helps, I usually use a Pause routine like:

VBA Code:
Sub Pause(Optional ByVal Period As Single = 1)
    Period = Timer + Period
    Do
        DoEvents
    Loop While Period > Timer
End Sub

And then just add a Pause after deleting the userform before having VBA proceed with the balance of your code.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

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