Write value to userform parent

[rybussell]

One of these days, I'm going to break down and buy a book and learn to do this myself... But what better way to learn than ask..

After searching for about 20 minutes, I'm lost. Here's the story:

I have an excel 2010 workbook with 5 UserForms we will call UserForm1, UserForm2, UserForm3, etc

One UserForm1 - UserForm4, I have a text box called enteredDate and a Select Date button..

On UserForm5, I have a date select calendar.

I don't want to have to code UserForm5 to for each UserForm because eventually there may be a couple dozen UserForms... What I would like is when UserForm5 gets opened from whatever UserForm1-4, and the date is entered and apply is hit, the parent of UserForm5 automatically has it's text box 'enteredDate' set to the date value from the calendar.

Thanks ahead of time...

[Norie]

So Userform5 is basically a pop-up calendar?

What you could use is a function.

In each userform module:

Private Sub btnSelectDate_Click()
     Me.txtEnteredDate.Value = GetDate
End Sub

In a standard module:

Function GetDate() As String
Dim frmCalendar As UserForm5

    Set frmCalendar = New UserForm5

    frmCalendar.Show
    GetDate = frmCalendar.Calendar1.Value
    Unload frmCalendar

    Set frmCalendar = Nothing

End Function

[rybussell]

example-rybussell.xlsm

Here is an example.

I don't exactly use a popup calendar. We debated at work which way to go and everyone seemed to agree this way was faster and just easier.

[Norie]

I can't download the file right now but I'll check it out later.

Did you try what I suggested?

It should work, with some minor changes, no matter what you have on Userform5.

[rybussell]

I tried everything I could think of with what you initially wrote and kept getting errors. Not sure where I'm missing something but I think once you can see the example, it will click... It's probably something obvious like parent.enteredDate.value or such...

[Norie]

What did you change in the code I posted?

The only things you should need to change are names.

By the way, if anything is the parent of a userform it's more likely to be the Excel application itself not the form it's called from.

[rybussell]

Yeah the names are all I changed to match them up with fields on the UserForm...

So what you're saying is if I use UserForm1 to open UserForm5 that they don't take a Parent - Child relationship?

[Norie]

As far as I know there is no relationship between the forms.

I suppose you could write some code that created one but that might be a bit complicated, especially if multiple forms were involved.

[rybussell]

Uncomplicated is my middle name so that's not likely going to happen...

If you see the excel book I uploaded later and figure something out, let me know.. Otherwise I guess I'll have to manually do everything and be really sad when I send a huge bill to my company for time spent.

[Norie]

Just had a look at the workbook and I'm kind of lost.

You appear to be calling Userform1-4 from Userform5.

Userform1-3 all seem to be the same with a textbox and a command button.

The command button on each of those forms shows Userform4 which has 3 comboboxes for month, day and year.

So, what exactly is the parent of what?

Also, and I meant to ask this straight off, why do you have 3 forms that are basically the same?

[rybussell]

My apologies... This isn't the actual book I'm using.. The actual book I'm using has a LOT of company sensitive information and deleting it would be more time consuming than throwing this together..

Ok to break down... I only created UserForm5 to give the visual of different forms...

What I want to do is, whether I open Userform1, 2, or 3 (which will be different on the final product), they should open UserForm4... And After I select a month, day, and year, and hit apply, I want UserForm4 to know which userform to apply it to.

So say I open UserForm2 and click select date and UserForm4 opens.. Enter date, etc, click apply. UserForm4 should close (that part I can do) and should detect that it was called from UserForm2 and set the enteredDate field to be whatever values from UserForm4 was.

[Norie]

The code I posted, with a few minor tweaks, can do that.

It will also help if I post all the code needed.

What's missing is the code for the userform that will be user to enter the date.

[Norie]

I've updated your workbook.

[mikerickson]

You could add a property to UserForm5.

' in UsedForm5 code module

Public myParent As Object

Private Sub txtEnteredDate_Change()
    With myParent.Controls(txtEnteredDate.Tag)
        .Value = txtEnteredDate.Text
    End With
End Sub

Which control in the calling UF can be specified with the .Tag property of the called text box

' in UF 1 code module

'...

Private Sub CommandButton1_Click()
    Dim aForm As New UserForm5
    With aForm
        Set .myParent = Me
        .txtEnteredDate.Tag = "ComboBox1"
        .Show
    End With
End Sub

' in UF2 code module
'...

Private Sub CommandButton1_Click()
    Dim aForm As New UserForm5
    With aForm
        Set .myParent = Me
        .txtEnteredDate.Tag = "TextBox1"
        .Show
    End With
End Sub

[mikerickson]

If the only thing that UserForm5 does is get the user's entry, you could put a function in it and use it like an InputBox.

'in Userform 5 

Public Function Value()
    Me.Show
    With UserForm5
        Value = .TextBox1.Text
    End With
    Unload UserForm5
End Function

Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Hide
End Sub

It would be called with code like

' in calling UF

Private Sub CommandButton2_Click()
    Dim uiValue As String

    uiValue = UserForm5.Value

    If uiValue = vbNullString Then
        MsgBox "Cancel pressed"
    Else
        Me.TextBox1.Text = uiValue
    End If
End Sub

[mikerickson]

Another way might be to look at the Userforms collection, on the assumption that Userform5 is the most recently invoked userform and that it was "called" by the second most recently invoked userform.

' in UserForm5

Private Sub CommandButton2_Click()
    With UserForms
        With .Item(WorksheetFunction.Max(0, .Count - 2))
            ' with the second most recently invoked userform
            .TextBox1.Text = Me.txtEnterBox.Text
        End With
    End With
End Sub

[rybussell]

I'll get to looking at yours mike here shortly, but for the time being, the updated one from Norie worked perfectly! Thanks! Rep added

[andrek666]

Hi Mike,

.Item(WorksheetFunction.Max(...

Should this be .Item(WorksheetFunction.Match(... ??

[FDibbins]

Hi Mike,

.Item(WorksheetFunction.Max(...

Should this be .Item(WorksheetFunction.Match(... ??

While your observation may be valid, this thread is almost 10 years old.

[andrek666]

Sorry. I've figured that Mike's code was correct. It needs Max function after all. It works now perfectly in my code. Yeah!