Show two UserForms and transfer data

**jagman** · 08-25-2008, 12:45 AM

I am attempting a project which will be based on a touchscreen and keyboard-less data entry. The idea is for most information to be obtained from drop-down lists in combo boxes on a user form with minimum need for direct entry of numbers or text. I have managed to create my first userform consisting of 3 combo boxes, 1 text box linked to a cell reference, and the last text box requiring a numerical entry. My second userform was constructed to resemble a simple handheld calculator. The numbered commandbuttons on the calculator put the resulting number into the "display" text box on userform2. Pressing the "ENTER" commandbutton on userform2 would then transfer the number to the active text box on the first userform. I assumed I would be able to show both userforms at the same time but I have not been able to achieve this. As I have fallen at the first hurdle I have not yet got to the transfer part. Is it possible to show two userforms at the same time and transfer data between them?
Thanks for taking the time to read my problem.
Thanks in advance.

**Dave69rock** · 08-25-2008, 01:18 AM

Hi

yes its possible to show two userforms at the same time. In Vb select your userform and show properties window (F4) find showModal and select false on both of your userforms.

The code for Command button or marco is
Userform1.show
Userform2.show

you can now work between the userforms and excel sheet without closing the forms down.

I'm not sure if the rest of what your planning is possible though, hope this helps

Dave

**jagman** · 08-25-2008, 01:59 AM

Thanks for your help, Dave.
That was the trick I needed to make some progress.
I can now start to sort out the data transfer.
Thanks again.
John.

**Dave69rock** · 08-25-2008, 02:18 AM

Hi John

Its not something I've tried in the past but i don't see why you can't achieve it

If you get stuck post your sheet on here and i will have a look at it.

Dave

**mikerickson** · 08-25-2008, 03:07 AM

There are a couple of ways to do this.
In the attached, you can invoke UserForm1, which will allow you to either invoke UserForm2 or UserForm3, each of which use different techniques to put the string entered in UF2 or 3's textbox in UserForm1's text box.

The QueryClose event of UserForm3 puts the UF3 textbox value directly into UF1's textbox at the time that UserForm3 is unloaded. (The Cancel and corner X do not change UF1.) It is a dedicated sub-UF of UserForm1 and is pretty useless unless called by UserForm1.

UserForm2 is used in a way that is closer to your situation. The UDF, UF2Input, (in a normal module) returns the value entered by the user in UserForm2. UF2's buttons don't unload it, they hide it so that the calling routine can poll the userform's .Tag property for the function's return value.
(Similar to an Application.InputBox, UF2Input returns the string "False" when Cancel or corner X is pressed.)

UserForm1 simply calls the function UF2Input as it would any other.

This approach (casting the userform's return value as a UDF) allows UF2 (or your calculator) to be called from any routine. It is not dedicated to UserForm1 as UF3 is.

A further feature is that UF2 can also invoke the UF2Input function itself, by pressing the Invoke Myself button, creating multiple instances of the same UserForm. In your case (calculator) this feature might be used as a substitute for parenthesis.

The attached file has two buttons on the worksheet. One invokes UserForm1, which can invoke either UF2 or UF3, contasting the two techniques.

The other button simply demos the UF2Input function without UserForm1.

I hope this helps.
I should mention that on my Mac, all useforms (including these) are modal.

(I really like the idea of a calculator returning its value as a UDF, similar to an InputBox. It just makes good sense for a calculator to be a "stand-alone" rather than subsidiary of a particular routine.)

**jagman** · 08-26-2008, 01:27 AM

Hi, Mike.
Thanks for taking the time to provide such a complete response to my data transfer issue. You are quite right in guessing that I was intending to use my calculator with several different forms within the workbook.
I suspect I am like many who frequent this forum, in that my ambition is far in excess of my ability with regard to vba programming, so some of your code I did not understand.
I have a macro button (autoshape) to show both forms together, side by side. I wanted the calculator to remain visible after the number was transferred, for future entries. I couldn't understand the code around making userform2 hidden while transferring data back to userform1. In the end I discovered a "brute force & ignorance" method as follows.
While in userform 2, clicking the "Enter" command button executes:

HTML Code:

What are the risks with this way compared to using your safeguarded, function method? (I am really trying to learn better programming practice).
Thanks again for your time and patience.
John.

**mikerickson** · 08-26-2008, 09:41 AM

To keep the calculator visible while working on something else requires that the calculator be modeless. As my post hinted, I use a Mac, which doesn't support modeless Userforms, so I'm not familiar with that aspect of your project.

To use the UDF approach, forget about UserForm1 for the moment and simply create a UDF that returns the value from the Calculator.
What one could do is design the ufCalculator (userform) to keep it's return value in the ufCalculator.Tag and then have a UDF (in a normal module) something like

Please Login or Register  to view this content.

The first part of that is to see if ufCalculator has been shown and show it if not.
The second part tests the .Tag property for "control" values, that have values that you can set (vbNullString-intial (unused by user) value, False-Cancel pressed) etc.

There is no "transfer" of data from ufCalculator to any other form. There is a function returning a value. Code like this would be found in UserForm1. Or anyother routine that you want. (note the test for Cancel)

Please Login or Register  to view this content.

(I'm waiting for some clever person to post the VB code to get values from Excel's built-in calculator)

**jagman** · 08-27-2008, 01:00 AM

Thanks very much for your help and instruction, Mike.
I will try to integrate this into my project and try it out.
If I have difficulty I will post back.
John