Passing values between userforms on different workbooks

[theta]

I cannot get any approach to work for this.

I have 20 workbooks all with their own userforms and date picker forms. They are all referenced to a single common workbook called master. I have created a datepicker in the master workbook for them all to now use.

I do not want to rewrite all of the existing userforms so I have introduced a command button next to the text box requiring input. This should open the datepicker from master and when "OK" is clicked it should then return the value to the specified object.

It would appear referencing userform objects across 2 different workbooks (although referenced) is not easy.


Example
======

I would click a command button UserForm1 within "Another.xls" workbook. I would like to open frmDatePicker from "Master.xls" and have the value returned to UserForm1.TextBox1

So I would like to be able to call it like a function e.g. DatePicker(Workbooks("Another.xls").frmDatePicker.TextBox1)

?

[snb]

Why don't you restrict this to 1 userform in the 'master' workbook ?

Datepicker'form' ????

[theta]

Because the project is comprised of 20 workbooks, all with references to the master workbook.

23,000+ lines of code.

This date picker form is not a calendar control, it is a userform that has sepecific selections that then need to be returned to a control as specified when it is called.

These controls may be on different userforms in completely different workbooks...

?

[snb]

You seldom need more than 1 userform in a project. Are you familiar with Multipage ?

You better use a frame to create a 'datepicker'.

[theta]

Hi snb

Lets say for example that I have 1 userform in each of these 20+ workbooks.

I want all of them to be able to call this single userform in master and receive the result of the user input e.g. a text box?

[mikerickson]

In the master book write a function in a normal module, that returns the value from the userform. (This example uses a TextBox)

[VBA]Function DTThing()
UserForm1.Show
DTThing = UserForm1.TextBox1.Text
Unload UserForm1
End Function[/VBA]


Then, from any workbook you can use

[VBA]myVariable = Application.Run("Master!DTThing")[/VBA]
To get the value of the function DTThing (which is the value that the user entered in Master's userform)

[snb]

To illustrate what I mean not using a separate calendar userform see the attachment.
put som etest in textbox1, then goto textbox2. You can change the calendar using the spinbutton (month + or -)
To select a date click the date you want to be entered into the corresponding textbox.

[theta]

In the master book write a function in a normal module, that returns the value from the userform. (This example uses a TextBox)

[VBA]Function DTThing()
UserForm1.Show
DTThing = UserForm1.TextBox1.Text
Unload UserForm1
End Function[/VBA]


Then, from any workbook you can use

[VBA]myVariable = Application.Run("Master!DTThing")[/VBA]
To get the value of the function DTThing (which is the value that the user entered in Master's userform)

Wow this works great! It is much cleaner than creating properties and doing GET LET etc. I had another issue that resulted (but now fixed)...but thoughts are welcome!

If the item looked up was a date e.g. 01/02/1976 this would be represented as a decimal 1976.0833333 recurring.

This would not work when pasted onto the sheet as the vlookup would fail (the number would be pasted with limited numeric precision). The dates on the sheet used for the lookup are made up as =1976+(2/12) which produces a floating number.

I got round it by looking up the real date 01/02/1976 and converting it to a decimal within the vlookup by UDF e.g.

=VLOOKUP(DateToDecimal("01/02/1976",B:C,2,0)

This seems to work as the number is not stored anywhere so remains a repeating decimal up to the IEEE standard (which will be the same for the items being looked up?)

[vba]
'Convert real date (01/06/1975) to decimal date (1975.5)
Public Function DateToDecimal(realdate As Date) As Double

DateToDecimal = Year(realdate) + ((Month(realdate) - 1) / 12)

End Function
[/vba]

[mikerickson]

Convert the date given from the DT picker into a string. The use code like
[vba]ActiveCell.Value = DateValue(dateString)[/vba]

[snb]

[VBA]Public Function revertformDecimaltodate(c00) As String
revertformDecimaltodate=format(dateserial(left(c00,4),(c00 mod 1) *12+1,1),"dd-mm-yyyy")
End Function [/VBA]

[Paul_Hossler]

To get the value of the function DTThing (which is the value that the user entered in Master's userform)

I like that technique

I can see a number of places in my macros that I can use it to simplify things

Paul

[mikerickson]

You might be interested in this technique. Make a userform with a text box, TextBox1, a label, Label1 and two command buttons, butOK, butCancel. Then put this code in the UF module.

[VBA]Public Function Value(Optional strPrompt As String = "Enter Something", Optional strDefault As String) As String
With Me
.Label1.Caption = strPrompt

With .TextBox1
.Text = strDefault
.SelStart = 0
.SelLength = Len(strDefault)
End With

.Show
End With

With UserForm1
Value = .TextBox1.Text
End With

Unload UserForm1
End Function

Private Sub butCancel_Click()
Unload Me
End Sub

Private Sub butOK_Click()
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.Text = vbNullString
End Sub[/VBA]
Note that the function Value is declared Public so it can be accessed from a normal module with code like this.

[VBA]Sub test()
Dim uiValue As String

uiValue = UserForm1.Value(strPrompt:= "Type Something", strDefault:= "old answer")

If uiValue = vbNullString Then
MsgBox "canceled"
Else
MsgBox uiValue & " was entered"
End If
End Sub[/VBA]

This is a simple example that emulates an InputBox, but if the purpose of a userform is to get input from the user (rather than a form whose purpose is to manipulate cells or somesuch) one can incoperate the Value returned as a function of that userform and put all the "set defaults", "show the form" etc machinery inside the userform's module.

Note that this is requires that the Sub test and the userform be in the same workbook.

[Paul_Hossler]

[vba]Public Function Value(Optional strPrompt As String = "Enter Something", Optional strDefault As String) As String
[/vba]

I did not know that you could use UserForm.functions like that -- VERY elegant

That will allow me to clean up a lot of messy global variables etc.

Thanks for sharing that technique

Paul

[snb]

To me it's a new technique, but maybe I'm missing something; I can't see any advantage compared to:

In the macromodule:

[vba]Public sn(10) As String
Sub test()
UserForm1.Show
MsgBox sn(4)
End Sub[/vba]

In the userform a textbox1 and only this code

[vba]Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
sn(4) = TextBox1.Text
End Sub[/vba]

Or applied as a UDF:
[VBA]Function simpel()
UserForm1.Show
simpel = sn(4)
End Function[/VBA]


PS. @Paul; if you use an array you can reduce the amount of messy global variables to 1.

[mikerickson]

Consider these three ways to get a value back from a userform. (This code is in normal modules)

[vba]' explicit calls

UserForm1.Show
myVariable = UserForm1.TextBox1.Text
Unload Userform1
'...
UserForm1.Show
myVariable2 = UserForm1.TextBox1.Text
Unload Userform1
'...
UserForm1.Show
myVariable3 = UserForm1.TextBox1.Text
Unload Userform1[/vba]

[vba]' function calls

myVariable = FromUserForm
'...
myVariable2 = FromUserForm
'...
myVariable3 = FromUserForm
'...


Function FromUserForm() As Text
UserForm1.Show
FromUserForm= UserForm1.TextBox1.Text
Unload Userform1
End Function
[/vba]
and
[vba]' uf has .Value property

myVariable = UserForm1.Value
'...
myVariable2 = UserForm1.Value
'...
myVariable2 = UserForm1.Value
'...[/vba][vba]'in userform module
'...
Property Get Value()
Me.Show
Value = UserForm1.TextBox1.Text
Unload UserForm1
End Property[/vba]
All three do the same thing, the last is just treating the userform as a class and writting a Property for that class.

They do the same thing, in different styles.

The advantage of the userform=custom class approach is that if you design a niffty userform that can be used in many projects (e.g. a PickFromList form), all you have to do is export/import the userform module. You don't have to remember to write the subsidiary UDF into a normal module of each project.

NOTE: when treating the uf as a class, after the .Show command DO NOT use the Me keyword. The user might corner click the uf closed and Me may not exist. Using the full name of the userform (UserForm1) rather than Me gets the default data from a new instance of the uf, (this was used above to test if the user OKed or Cancled)

[Paul_Hossler]

The advantage of the userform=custom class approach is that if you design a niffty userform that can be used in many projects (e.g. a PickFromList form), all you have to do is export/import the userform module. You don't have to remember to write the subsidiary UDF into a normal module of each project.

The first thing I noticed was the re-usability and the modularity of the 'class/property' technique

Even within the same project I'll reuse the same userform, for info / warning / error messages, etc.

Paul

[GTO]

...NOTE: when treating the uf as a class, after the .Show command DO NOT use the Me keyword. The user might corner click the uf closed and Me may not exist. Using the full name of the userform (UserForm1) rather than Me gets the default data from a new instance of the uf, (this was used above to test if the user OKed or Cancled)

Greetings All,

@mikerickson:

Hi Mike :-) Reference post 12, I heartily agree with Paul; that is just "too cool!".

A lazy Sunday in effect, I tried a couple of things. In gist, I was thinking that it would be neat if we could create a default property (okay, not spectacularly neat, but just thought it might be a little "cool"). Alas, it appears one cannot specify a default member of a userform. I tried a little Class with a default (that calls the form), but of course the extra step makes this to no advantage at all.

Anyways, in calling the form directly, unless I'm glossing over something, how about using QueryClose as a final check on what we want the Function to return? This way Me is never an issue.

Form named frmInput:

One textbox, one label, two buttons, named as shown.

Option Explicit
 
Private bOK As Boolean
 
Public Function Value_Ret(Optional strTitle As String = "My Custom Input", _
                          Optional strPrompt As String = "Enter Something", _
                          Optional vntDefault As Variant = vbNullString _
                          ) As String
 
    With Me
        .Caption = strTitle
        .lblPrompt.Caption = strPrompt
        With .txtInput
            .Value = vntDefault
            .SelStart = 0
            .SelLength = Len(vntDefault)
        End With
        .Show
 
        If Len(.txtInput.Value) > 0 And Not .txtInput.Value = vntDefault Then
            Value_Ret = .txtInput.Value
        Else
            Value_Ret = False
        End If
    End With
    'Unload frmInput
End Function
 
Private Sub cmdCancel_Click()
    Unload Me
End Sub
 
Private Sub cmdOK_Click()
    bOK = True
    Unload Me
End Sub
 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Not bOK Then Me.txtInput.Value = vbNullString
End Sub

In a Standard Module

[vba]Sub example02()
Dim uiValue

uiValue = frmInput.Value_Ret(, , "old text")
If uiValue = "False" Then
MsgBox "cancelled"
Else
MsgBox uiValue & " was entered."
End If
End Sub[/vba]

Well, a pleasant evening to all,

Mark

[theta]

I like how this thread is evolving. The result should be put into the KB, along with this research and discussion into the best method(s) available.

[mikerickson]

The QueryClose approach seems a good one. I was into the Me/Userform1 distinction because I got into this from the point of view of multiple instances of a userform, but the Q.Close removes the need for a (possible) second instance of the uf.

[GTO]

Thank you for the feedback Mike and glad it seemed like a decent way. I'm afraid you're way ahead of me in conceptualization. Shucks, if I think about multiple instances of anything, usually my poor head just starts hurting (LOL).