VBA excel Reference Userform as variable to get data

Question

I have a few days on this problem. i am using excel VBA to fill in a Word Doc. I have multiple Userforms that contains textBoxes, ComboBoxes etc. in order to keep code small and not to create the same module over and over i need to change only the FormName

this code is working to fill in the Word Doc

wDoc.SelectContentControlsByTitle("#MonthBox").Item(1).Range.Text = Request_Form_1.MonthBox.Value

but i want to use variable to reference the Userform in a module but i get all types of errors

Dim FormName As String
FormName = "Request_Form_1"
wDoc.SelectContentControlsByTitle("#MonthBox").Item(1).Range.Text = FormName.MonthBox.Value

Kind regards

Thank you for your support What i had tried and is not working

1. Tried UserForms(NumeForma) MsgBox UserForms(NumeForma).LunaBox.Value the form is visible but i get error 13 type mismatch
2. Tried to insert a module that include ShowAnyForm() call MsgBox ShowAnyForm(NumeForma).LunaBox.Value error "Expected Function or variable"
3. Change in module ShowAnyForm() from Sub to Function using MsgBox ShowAnyForm(NumeForma).LunaBox.Value error 424 object required

Answer 1

Everything just in front or after a dot (like FormName.) can't be (easy) replaced by a variable as it's an object or a member (but you can CallByName it!).

Variables have to be passed as argument to a parameter, but as many objects are accessible by a collection, that's the way we usually go.

Loaded userforms can be found in the UserForms collection. But they can only be accessed by their item index (0 to UserForms.Count() -1), not their name. So you need sth. like:

'code by http://www.cpearson.com/Excel/showanyform.htm
For Each Obj In VBA.UserForms
     If StrComp(FormName, Obj.Name, vbTextCompare) = 0 Then ' If Obj.Name matches FormName loop is exited and Obj contains the UserForm reference of FormName.
         Exit For
     End If
Next Obj

to get an loaded userform reference. Samplecode is availible at Show Any Form.

Your code should look like:

wDoc.SelectContentControlsByTitle("#MonthBox").Item(1).Range.Text = ControlValueByName(FormName:=FormName, ControlName:="MonthBox", _
        ProcName:="Value", CallType:=vbGet)

I add a function to return UserForm reference for a given FormName:

'Code inspired by http://www.cpearson.com/Excel/showanyform.htm
Public Function UserFormByName(ByVal FormName As String) As UserForm
Dim Obj As Variant
For Each Obj In VBA.UserForms
    If StrComp(FormName, Obj.Name, vbTextCompare) = 0 Then
        Exit For
    End If
Next Obj
If Obj Is Nothing Then ' UserForm not loaded
    Err.Clear
    Set Obj = VBA.UserForms.Add(FormName)
    If Err.Number <> 0 Then
        MsgBox "Error Calling Form: " & FormName
        Exit Function
    End If
End If
Set UserFormByName = Obj
End Function

Now you can use:

wDoc.SelectContentControlsByTitle("#MonthBox").Item(1).Range.Text = UserFormByName(FormName).MonthBox.Value

Things get easier if you call a method from the userform, as you can simply pass the reference to it. See OP's answer

On form:

Private Sub InregistreazaButtonActive2_Click()
    ToWord Me
End Sub

In module:

Sub ToWord(ByRef Caller As UserForm)
    MsgBox Caller.AnBox.Value
End Sub

Instead of the forms reference, you can pass the listbox reference, that contains the form reference too, as it is a child object the form, so form is ListBox.Parent.

Private Sub InregistreazaButtonActive2_Click()
    ToWord Me.AnBox
End Sub

Sub ToWord(ByRef CallerListBox As ListBox)
    MsgBox CallerListBox.Value
    Debug.Print CallerListBox.Parent.Name ' shows form name of listbox 
End Sub

By passing references you don't have to worry about multiple instances (that woud have same name) as each instance has its own reference.