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:

open conditionalBuilder
double-click Field text box
mappingGuide opens
pick a smartytag from listbox
fill smartytag value into field text-box in conditionalBuilder
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.

@cyboashu - can you please elaborate or draw it out in an answer or provide a link for more information? — Scott Holtzman
– Scott Holtzman, Commented Feb 5, 2018 at 16:58
@CallumDA, you're right, that's why i upvoted your and [cyboashu] answer. It's just another idea. ;) — Maciej Los
– Maciej Los, Commented Feb 5, 2018 at 19:57

cyboashu · Accepted Answer · 2018-02-05 17:17:41Z

3

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

answered Feb 5, 2018 at 17:17

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Holtzman Over a year ago

thanks. I'll have to study this. this goes beyond my current level of understanding but I think could be very useful.

CallumDA · Accepted Answer · 2018-02-05 17:33:14Z

3

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)

edited Feb 5, 2018 at 17:33

answered Feb 5, 2018 at 17:28

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

4 Comments

Scott Holtzman Over a year ago

this is pretty awesome, too. thank you. i'll have to study this since it's a bit new for me. thanks again ;)

CallumDA Over a year ago

Happy to help! If you’ve got any questions ask away :)

Scott Holtzman Over a year ago

The one question I have is that the code for the two forms in your example, still goes into the userform module, correct? And I can use With Events in the mappingGuide form as well, correct, because that will be called on it's own as well with certain actions taken depending on user choice.

CallumDA Over a year ago

Yes it does and yes you can - although if I remember correctly a quirk of VBA is that you can't compile your Presenter with Private WithEvents MG... until you have the corresponding Public Event SomeEvent in your MappingGuide

Scott Holtzman · Accepted Answer · 2018-02-05 17:03:43Z

1

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

answered Feb 5, 2018 at 17:03

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Collectives™ on Stack Overflow

VBA Input Value From Another UserFormB into TextBox From UserFormA

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related