VBA Input Value From Another UserFormB into TextBox From UserFormA

Question

I have a userForm (mappingGuide) that allows user to pick a smartyTag from a list of more user-friendly names.

I have a second user-form (conditionalBuilder) that I would like to call this userForm upon double-clicking a text field so that a user can lookup which smartyTag to apply (in case they don't know).

So logic, is:

1. open conditionalBuilder
2. double-click Field text box
3. mappingGuide opens
4. pick a smartytag from listbox
5. fill smartytag value into field text-box in conditionalBuilder
6. unload mappingGuide

The issue I think I having with completing the requirement is that when I load the forms themselves I cannot find a way to set the text of the fieldName textbox of the loaded instance of conditionalBuilder (see last code block below). I've been searching around, but cannot figure it out.

Here is relevant code:

conditionalBuilder loads from Custom UI ribbon

Sub RunCode(ByVal Control As IRibbonControl)

    Select Case Control.ID

        Case Is = "mapper": LoadMappingGuide
        Case Is = "conditional": LoadConditionalBuilder

    End Select

End Sub

Sub LoadConditionalBuilder()

    Dim conditionalForm As New conditionalBuilder
    conditionalForm.Show False

End Sub

double-click event of fieldName then loads mappingGuide

Private Sub fieldName_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Me.hide
    Dim pickField As New mappingGuide
    pickField.Show False

End Sub

smartTag listbox click event then attempts to place selection into fieldName (or selection if form not loaded)

Private Sub smartTagList_Click()

    If smartTagList.ListIndex > -1 And smartTagList.Selected(smartTagList.ListIndex) Then

        Dim smartyTag As String
        smartyTag = smartTagList.List(smartTagList.ListIndex, 2)

        If isUserFormLoaded(conditionalBuilder.Name) Then
            '*** ---> below is my issue how to reference instance of form 
            conditionalBuilder.fieldName.Text = smartyTag
            conditionalBuilder.Show
        Else
            Selection.Range.Text = smartyTag
        End If

    End If

    Unload Me

End Sub

If there is a better set-up that would be great to know too. I have the forms separate because there's a couple of levels a user can create tags with.

Answer 1

This is how I would do it, a bit of overkill but in case of multiple forms it will be beneficial.

Module 1:

 Option Explicit

    Sub test()
        frmMaster.Show False
    End Sub

Form 1 : frmMaster:

Option Explicit
'/ Declare with events
Dim WithEvents frmCh As frmChild

Private Sub TextBox1_DblClick(ByVal cancel As MSForms.ReturnBoolean)
    handleDoubleClick
End Sub
Sub handleDoubleClick()

    If frmCh Is Nothing Then
        Set frmCh = New frmChild
    End If

    frmCh.Show False

End Sub

'/ Handle the event
Private Sub frmCh_cClicked(cancel As Boolean)
    Me.TextBox1.Text = frmCh.bChecked
End Sub

Form 2: frmChild:

Option Explicit

Event cClicked(cancel As Boolean)
Private m_bbChecked As Boolean

Public Property Get bChecked() As Boolean
    bChecked = m_bbChecked
End Property

Public Property Let bChecked(ByVal bNewValue As Boolean)
    m_bbChecked = bNewValue
End Property

Private Sub CheckBox1_Click()
    Me.bChecked = Me.CheckBox1.Value
    '/ Raise an event when something happens.
    '/ Caller will handle it.
    RaiseEvent cClicked(False)
End Sub

Answer 2

You can do this with a presenter class which controls userform instances and pass values between them. I mocked up something similar to give you an idea.

Presenter.
This is a class module which creates the userforms, controls their scope, and catches the event thrown by the ConditionalBuilder. It makes it super easy to pass values between userforms.

Private WithEvents CB As ConditionalBuilder
Private MG As MappingGuide

Public Sub ShowCB()
    Set CB = New ConditionalBuilder
    CB.Show vbModal
End Sub

Private Sub CB_ShowMappingGuide()
    Set MG = New MappingGuide
    MG.Show vbModal
    CB.UpdateTB1 Value:=MG.SmartTag
End Sub

ConditionalBuilder.
This has a simple function to update your textbox and also an event which raises actions in the presenter.

Public Event ShowMappingGuide()

Public Function UpdateTB1(Value As String)
    TextBox1.Value = Value
End Function

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    RaiseEvent ShowMappingGuide
End Sub

MappingGuide.
The Type and Property could be overkill since we just want one value from the mapping guide but it's still good practice.

Private Type TView
    Tag As String
End Type
Private this As TView

Public Property Get SmartTag() As String
    SmartTag = this.Tag
End Property

Private Sub UserForm_Initialize()
    Tags.List = Array("a", "b", "c")
End Sub

Private Sub Tags_Click()
    this.Tag = Tags.List(Tags.ListIndex, 0)
    Me.Hide
End Sub

I have one final Standard Module which creates the Presenter. This is what you'd hook up to your ribbon.

Public Sub ShowProject()
    With New Presenter
        .ShowCB
    End With
End Sub

---

Step 1 (double click text field)

---

Step 2 (selecting "b")

---

Step 3 (result)

Answer 3

I actually solved it by placing the below block inside the IF where I check for the form being loaded and I will leave open for better answers, if there are any.

    Dim uForm As Object
    For Each uForm In VBA.UserForms
        If uForm.Name = conditionalBuilder.Name Then
            uForm.fieldName.Text = smartyTag
            uForm.Show
        End If
    Next