Adding userform interferes with separate procedure

[Jonnyoforem]

VBA novice here so pardon my lack of understanding. I have a procedure, Sub addTraining () that when isolated works just fine and does what I want it to do. However when i run a separate procedure, Sub addEmployee (), the addTraining procedure starts giving me an error.

Sub addEmployee ()
     Dim k As String
     Dim i As integer

'Find the userform that the user specifies
i = 1
Do While Cells(i, 3) <> ""
    If Cells(i, 3).Value = ComboBox1.Value _
    Then k = Cells(i, 5).Value
    
    i = i + 1


Loop

'Display the specified UserForm
VBA.UserForms.Add(k).Show 

End Sub

After running this section of code

VBA.UserForms.Add(k).Show

I start getting errors in the addTraining procedure. Here's the relevant code for addTraining

Sub addTraining
     Dim MyUserForm As Variant
     
     'cmb is a value taken from a combobox
     'of a different userform
     cmb = columnInsert.insertCombo.Value

     Set MyUserForm = ThisWorkbook.VBProject.VBComponents (cmb & "Form")

     hght = MyUserForm.Properties("Height")
    hght = hght + 29

End Sub

The error occurs on the following line

hght = MyUserForm.Properties("Height")

And the error is: Run-time error '-2147467259 (80004005)': Method 'Properties' of object'_VBComponent' failed

Thanks in advance

 

[RoryA]

You can't change the properties while an instance is loaded. Why are you trying to change the design time property rather than changing the height of a loaded form?

 

[Jonnyoforem]

Thanks for replying Rory. These macros are part of a much larger project and I do need to have the height property changed at design time. Regarding the loaded instance, is there a way to unload an instance so I can change the properties?

 

[RoryA]

Why do you need that? It's quite unusual.

 

[Jonnyoforem]

The best way I can explain it is that I need a user form with a few event handlers that run their own macros. The user form will have textboxes where the user can type in dates. After typing the dates in, the user clicks a command button which uses a macro to transfer the dates onto a worksheet. In the future more textboxes may be added, that's why I was adjusting the height of the userform.

Can a userform created at runtime have an event handler? If it can then maybe that's the better way to go. If not then I'll need to find a way to unload the instance so I can change the design time properties.

This project will not be used by me at all. People with no vba experience will be entering the dates in the textboxes.

Hope that all makes sense. Thanks again for the help Rory

 

[RoryA]

That doesn't explain why you need to change the property of the actual form class rather than simply altering the Height of the form you actually loaded?