Move value from one UserForm to another (variable) UserForm

Question

I have adapted Eric Bentzen date picker to my needs (website and original file), now I am trying to use it for two different UserForms, but trying to avoid adding two separate buttons dedicated to moving entered date.

All the time it is either first UserForm (lets call it ApplicationUF) open or second one (lets call it AnswersUF), but never both at the same time.

Both forms have TextBox for entering date. My idea, that after click on that TextBox date-picker UserForm shows up, user pick date, clicks save button, date-picker UserForm is closed and entered date moved to active UserForm dedicated date TextBox. But I am having trouble referring to active UserForm.

TLDR: how to determine which UserForm is active and move values from another UserForm to it?

My code so far:

In date-picker UserForm chosen date variable is declared as public variable in separate module Public datFirstDay As Date

Now I am trying to add on_clik event to save button in date-picker UserForm, which determines active UserForm and moves entered date. My code so far:

Private Sub cmdSave_Click()
   'I am having problem determining which use form is open
   'I figured out that syntax should probably look something like this
   'but this obviously doesent work since as far as I understand IsLoaded used only in Acces
   'how can I adapt it to excel?
If VBA.UserForms("ApplicationUF").IsLoaded = True Then
    ApplicationUF.rDateTB.Value = datFirstDay
        ElseIf VBA.UserForms("AnswersUF").IsLoaded = True Then
          AnswersUF.DateTB.Value = datFirstDay
    End If

cmdCancel_Click
End Sub

I do not think it is important, but just in case I adding cmdCancel_Click sub code. It is original, unchanged code written by Eric Bentzen.

Private Sub cmdCancel_Click()

Set colLabelEvent = Nothing
Set colLabels = Nothing
bSecondDate = False
sActiveDay = Empty
lFirstDay = 0
Unload Me

End Sub

What do you mean by "move values"? Can't you just put these values in public variables so they can been "seen" anywhere? — ashleedawg
I am planing to use that, but I want it to autofill only by On_click event which would close date-picker UserForm and populate another active UserForm text box using public variable set in date-picker UserForm. I want user to be able to fill that text box by hand or by date picker. I am quite new so I might missing some other way. — Gexas

ashleedawg ashleedawg · Accepted Answer · 2018-10-04T06:26:25

To check if a UserForm is loaded (aka, visible) you can use this function:

Public Function formLoaded(frmName As String) As Boolean
    Dim f
    For Each f In UserForms
        Debug.Print f.Name
        If f.Name = frmName Then formLoaded = True
    Next f
End Function

Example Usage:

If formLoaded("UserForm1") then Unload UserForm1

As for passing information between the forms I would use public variables.

Move value from one UserForm to another (variable) UserForm

1 Answers