PDA

View Full Version : Passing values between userforms on different workbooks



theta
01-15-2013, 04:30 AM
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
01-15-2013, 07:43 AM
Why don't you restrict this to 1 userform in the 'master' workbook ?

Datepicker'form' ????

theta
01-15-2013, 08:01 AM
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
01-15-2013, 09:13 AM
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
01-15-2013, 10:14 AM
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
01-15-2013, 01:45 PM
In the master book write a function in a normal module, that returns the value from the userform. (This example uses a TextBox)

Function DTThing()
UserForm1.Show
DTThing = UserForm1.TextBox1.Text
Unload UserForm1
End Function


Then, from any workbook you can use

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

snb
01-15-2013, 03:38 PM
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
01-16-2013, 03:41 AM
In the master book write a function in a normal module, that returns the value from the userform. (This example uses a TextBox)

Function DTThing()
UserForm1.Show
DTThing = UserForm1.TextBox1.Text
Unload UserForm1
End Function


Then, from any workbook you can use

myVariable = Application.Run("Master!DTThing")
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?)


'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

mikerickson
01-16-2013, 01:32 PM
Convert the date given from the DT picker into a string. The use code like
ActiveCell.Value = DateValue(dateString)

snb
01-17-2013, 04:04 AM
Public Function revertformDecimaltodate(c00) As String
revertformDecimaltodate=format(dateserial(left(c00,4),(c00 mod 1) *12+1,1),"dd-mm-yyyy")
End Function

Paul_Hossler
01-17-2013, 07:36 AM
To get the value of the function DTThing (which is the value that the user entered in Master's userform)

I like that technique :beerchug:

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

Paul

mikerickson
01-17-2013, 08:01 AM
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.

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
Note that the function Value is declared Public so it can be accessed from a normal module with code like this.

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

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
01-17-2013, 06:48 PM
Public Function Value(Optional strPrompt As String = "Enter Something", Optional strDefault As String) As String



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

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

Thanks for sharing that technique

Paul

snb
01-18-2013, 01:12 AM
To me it's a new technique, but maybe I'm missing something; I can't see any advantage compared to:

In the macromodule:

Public sn(10) As String
Sub test()
UserForm1.Show
MsgBox sn(4)
End Sub

In the userform a textbox1 and only this code

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

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


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

mikerickson
01-18-2013, 09:05 AM
Consider these three ways to get a value back from a userform. (This code is in normal modules)

' 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

' function calls

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


Function FromUserForm() As Text
UserForm1.Show
FromUserForm= UserForm1.TextBox1.Text
Unload Userform1
End Function

and
' uf has .Value property

myVariable = UserForm1.Value
'...
myVariable2 = UserForm1.Value
'...
myVariable2 = UserForm1.Value
'...'in userform module
'...
Property Get Value()
Me.Show
Value = UserForm1.TextBox1.Text
Unload UserForm1
End Property
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
01-18-2013, 04:59 PM
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
01-20-2013, 08:24 PM
...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!".:bow:

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

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

Well, a pleasant evening to all,

Mark

theta
01-21-2013, 03:24 AM
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
01-21-2013, 02:28 PM
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
01-21-2013, 03:30 PM
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).

mikerickson
01-22-2013, 06:51 AM
^^^
The Scots invented an elixir thats good for a hurting head. And it works!
But too many instances of that elixir can cause my head to hurt, later.


"Beware of strong drink. It can cause you to shoot at tax collectors. And miss."