Creating a userform on the fly and deleting it when done - naming the userform

[AndyGalloway]

I have some code. It used to create a userform called "myUserForm". However, now it just creates "UserForm4", then the next time it is run it creates "UserForm5", then "UserForm6", etc. I must have made a change to something, but I can't figure out what that something could be. I have only included down to the second line of code because by this time "UserForm4" has already been created in the VBE where previously "myUserForm" used to be created by this stage. I have tried declaring myUserForm as an object, but that didn't work. I am using Excel 2016. What am I doing wrong?

Interestingly, I have a lot of code that comes after this code that refers to the userform and the controls on it as "myUserForm". However, when I come to delete it or refer to it in the "Cancel" routine, "myUserForm" is nowhere to be found. If I use "UserForm4" for these commands, it works perfectly. However, I want the userform to be deleted on exit and created again the next time it is needed.

Dim myUserForm As VBComponent


    Set myUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With myUserForm
    ...

 

[AndyGalloway]

Some further information. When I open the workbook and run the code below, I end up with a userform called "myUserForm". When I run the code again, myUserForm is detected and removed then Exit Sub (temporary measure). When the code is run again, I get "UserForm4" and an error. However, if instead of running the code for the third time, I close the workbook and open it again, when I run the code I get "myUserForm". It seems that in order to fully remove the userform from the project, I have to close the application and re-open it. Is this correct? Or is there a simple command that has the same effect?

    ' First, check the form doesn't already exist
    For n = 1 To ActiveWorkbook.VBProject.VBComponents.Count
        If ActiveWorkbook.VBProject.VBComponents(n).Name = "myUserForm" Then
            With ActiveWorkbook.VBProject
                .VBComponents.Remove .VBComponents("myUserForm")
                '.Remove .VBComponents("myUserForm")
                Exit Sub
            End With
        End If
    Next n
    
    Set myUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With myUserForm
        .Properties("Caption") = "Re-order Job Titles"
        .Properties("Name") = "myUserForm"
        ' Expand the form size
        .Properties("Height") = 165 + (18 * varListCount)
        .Properties("Width") = 330
        .Properties("StartUpPosition") = 0
        If .Properties("Height") > Application.Height Then
            .Properties("Top") = Application.Top
            .Properties("Height") = Application.Height
            .Properties("KeepScrollBarsVisible") = fmScrollBarsVertical
            .Properties("ScrollBars") = fmScrollBarsVertical
            .Properties("ScrollHeight") = 130 + (18 * varListCount)
        Else
            .Properties("Top") = AppYCenter - (.Properties("Height") / 2)
            .Properties("KeepScrollBarsVisible") = fmScrollBarsNone
            .Properties("ScrollBars") = fmScrollBarsNone
            .Properties("ScrollHeight") = 0
        End If
        .Properties("Left") = AppXCenter - (.Properties("Width") / 2)
        .Properties("BackColor") = &H8000000F
        
        Debug.Print .Properties("Name")

 

[Logit]

.
Here is a different approach. Download workbook : https://www.amazon.com/clouddrive/share/moPW99Lxu1kZGI9ZLtVTvEBFnS8h4ofG57KMMRNo9qT

Public NewForm As Object, NewClass As Object


Sub ShowForm()
'****** This sub calls the modules to create the userform and add the class module ******
'Create UserForm
Application.ScreenUpdating = False
    Call CreateForm
    
    ' Add UserForm code and Class Module
    Call AddClass
Application.ScreenUpdating = True


    VBA.UserForms.Add (NewForm.Name)
    UserForms(1).Hide
    UserForms(1).Show


    'Remove form and class module
    ThisWorkbook.VBProject.VBComponents.Remove vbcomponent:=NewForm
    ThisWorkbook.VBProject.VBComponents.Remove vbcomponent:=NewClass
End Sub
Private Sub CreateForm()
    
    Dim NewImage As MSForms.Image
    Dim iRow As Integer, iCol As Integer
    Dim PaletteColours
    Dim TopPos As Integer, LeftPos As Integer
    
    'Add a new Forms module
    Set NewForm = ThisWorkbook.VBProject.VBComponents.Add(3)


    'Set the Form size
    NewForm.Properties("Width") = 200
    NewForm.Properties("Height") = 195
    TopPos = 6


    
End Sub


Private Sub AddClass()


    Dim NewMod As Object
    'Delete the Class Module "Colours" if it exists
    On Error Resume Next
    Set NewClass = ThisWorkbook.VBProject.VBComponents("Colour")
    ThisWorkbook.VBProject.VBComponents.Remove NewClass
    On Error GoTo 0


    'Add the Class Module to capture the Image Click Event
    Set NewClass = ThisWorkbook.VBProject.VBComponents.Add(2)
    NewClass.Name = "Colour"


    With NewClass.CodeModule
        .InsertLines 2, _
                     "Public WithEvents ClrCntrl As Image" & Chr(13) & _
                     "Private Sub ClrCntrl_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal y As Single)" & Chr(13) & _
                     "ClrCntrl.SpecialEffect = fmSpecialEffectSunken" & Chr(13) & _
                     NewForm.Name & ". backcolor = ClrCntrl.backcolor" & Chr(13) & _
                     NewForm.Name & ".Caption = ""Control colour is "" & ClrCntrl.BackColor" & Chr(13) & _
                     "End Sub"
    End With


    'Add the code for the UserForm
    Set NewMod = ThisWorkbook.VBProject.VBComponents(NewForm.Name)
    With NewMod.CodeModule
        .InsertLines 2, _
                     "Private AA(1 To 56) As New Colour" & Chr(13) & _
                     "Private Sub UserForm_Initialize()" & Chr(13) & _
                     "Dim IM As Control, i as Long" & Chr(13) & _
                     "i = 1" & Chr(13) & _
                     "For Each IM In " & NewForm.Name & ".Controls" & Chr(13) & _
                     "Set AA(i).ClrCntrl = IM" & Chr(13) & _
                     "i = i + 1" & Chr(13) & _
                     "Next" & Chr(13) & _
                     "End Sub"
    End With
End Sub

In ThisWorkbook module :

Private Sub Workbook_Open()
    ShowForm
End Sub

For what it is worth ... I tried a similar macro/s as you just posted. I too couldn't get the userforms to name correctly nor delete as required. Not certain why the command line to remove the form fails.

 

[AndyGalloway]

.
Here is a different approach. Download workbook : https://www.amazon.com/clouddrive/share/moPW99Lxu1kZGI9ZLtVTvEBFnS8h4ofG57KMMRNo9qT

Logit, thanks for the swift reply. I have downloaded your file and I'm playing with it this morning. I'm not absolutely certain when the CodeModule code should run. In the copy that I am playing with, it does not run on the mousedown event or the initialize event (as far as I can tell). I will try adding a commandbutton to the form to ensure that I can get code to run after creating this form. If so, this may be just what I am looking for. Thanks. For my edification and education, why have you put code in the mousedown event and the initialize event? If I removed that code and instead created a button to which I will attach the code, would that affect the creation or deletion of the userform? Also, why did you decide to create a new class?

It would still be more useful if I could name the userform myself and refer to it by that name. Adding a new userform every time you want to use a bit of functionality doesn't seem a very elegant way to go. So, this is either an Excel bug or I am missing a vital piece of information.

 

[My Aswer Is This]

I'm just curious. Why would you want to create a UserForm on the fly?
Why not create a UserForm once and just use the same UserForm over and over.

You know you could create a UserForm and put it in your Personal folder and use it on any workbook.

 

[AndyGalloway]

For my edification and education, why have you put code in the mousedown event and the initialize event? If I removed that code and instead created a button to which I will attach the code, would that affect the creation or deletion of the userform? Also, why did you decide to create a new class?

I have tried amending your AddClass code to look like this. However, but the code that should be added to the click event of the button ends up in the wrong place and creates a compile arroe before the form is fully instantiated. Any thoughts?

Private Sub CreateForm()
    
    Dim NewImage As MSForms.Image
    Dim iRow As Integer, iCol As Integer
    Dim PaletteColours
    Dim TopPos As Integer, LeftPos As Integer
    Dim objButton As MSForms.CommandButton
    Dim myString1 As String
    Dim z As Long
    
    'Add a new Forms module
    Set NewForm = ThisWorkbook.VBProject.VBComponents.Add(3)


    'Set the Form size
    NewForm.Properties("Width") = 200
    NewForm.Properties("Height") = 195
    TopPos = 6
    
    Set objButton = NewForm.Designer.Controls.Add("Forms.CommandButton.1", "btnTest", True)
    With objButton
        .Caption = "Test"
        .Left = 100
        .Width = 66
        .Height = 24
        .Top = 76
    End With


    With NewForm.CodeModule
    myString1 = "    MsgBox ""That worked"", vbOKOnly, ""Success!"""
        z = .CountOfLines
        .InsertLines z + 1, "Sub btnTest_Click()"
        .InsertLines z + 2, "    "
        .InsertLines z + 3, myString1
        .InsertLines z + 4, "    "
        .InsertLines z + 5, "End Sub"
    End With


End Sub

 

[AndyGalloway]

I'm just curious. Why would you want to create a UserForm on the fly?
Why not create a UserForm once and just use the same UserForm over and over.

You know you could create a UserForm and put it in your Personal folder and use it on any workbook.

Because I don't know how many controls will be required on the userform until run time. I could have just a blank form and add controls as they are required, but some of the controls will be commandbuttons with coding attached to the click event. Apparently, you can't add coding to a control in a userform that already exists. The only way to do what I need is to create the whole userform and all the controls in it from scratch at run time.

 

[Logit]

.
The workbook code is set to open the dynamically created form when the workbook is opened. The button allows the user
to dynamically create a userform again if the initial instance userform was closed.

I don't understand why the workbook does not function like the above, for you there.

The code is not mine. It is an adaptation of code found on the internet whose original purpose was to create a dynamic
userform populated with system colors. The purpose was a color chart for selecting different colors.

I have to agree with "My Answer Is This" .... why go to the trouble of dynamically creating a userform when it is more
effective and easier to simply have a userform present at all times that can be displayed when required ?

Consider also that Excel is not designed to be secure. Anyone who knows how to get to the VBE can review the code therein
that dynamically creates the form. Doing so allows the curious individual to do whatever they want with the workbook.

 

[Logit]

.
We posted almost at the same time and I did not see your latest response until later.

Look at this resource .. there are many more on the internet :

https://www.ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson21.htm

 

[My Aswer Is This]

Thanks for that answer. I have never tried to create a Userform on the fly. Always adding the needed controls and the code needed to run that control.

But I guess you have this mostly figured out and think this is the best way to do things.
That must take a lot of coding. But glad to see you know how to do this mostly.

Take care.