Solved

pass data from a userform to a userform word vba

Posted on 2010-09-03
7
924 Views
Last Modified: 2012-08-14
I have a userform that gathers configuration data from the user. When the OK button is clicked, a subroutine in the userform module goes through each textbox in order and does a variety of calculations and assignments. There are 3 textboxes that if they have content in them,  I need to gather some more specific data. To do this I pop up a second userform.  When the OK button on that form is clicked, I want to return a value to the original calling userform subroutine.

How do I return the value (let's call it price) so that I can incorporate it into my calculations?

0
Comment
Question by:karimjohnson
  • 4
  • 3
7 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33602394
Casll the second form from the first BUT when clicking the second button HIDE the second form:

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Processing returns to your first userform and the data is displayed and in the outline below the form is redisplayed but of course you can use the data and store it somewhere ending with an unloaf of userform1 if required.

Chris
Private Sub CommandButton1_Click()

    Me.Hide

    Load UserForm2

    UserForm2.Show

    Me.TextBox1 = UserForm2.TextBox1.Text

    Unload UserForm2

    Me.Show

End Sub

Open in new window

0
 

Author Comment

by:karimjohnson
ID: 33603412
If I understand correctly, this assumes I am passing the value of a userform field in userform2 back to a userform field in userform1. Is there a way to pass a calculated value from userform2 (for example, listbox1*listbox2) back to a variable set in the module associated with userform1 (rather than a form field)? Otherwise, I think I will have to create hidden fields for the return values (since they are not part of what I want the user to see).
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33603699
Any data from userform2 is passed back to userform1 by such a mechanism whether userform1 does the calculation on teh data or whether userform2 does the calculation and it's made available to userform1.

Chris
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:karimjohnson
ID: 33604024
so I could modify it like so?

Sub cmdOK_Click()
[other code here...]

    Me.Hide
    Load UserForm2
    UserForm2.Show
    Me.CalculatedPrice= UserForm2.CalculatedPrice 'where calculated price is listbox1 * listbox2 after OK
    Unload UserForm2
    Me.Show
End Sub
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33604032
INdeed.

Chris
0
 

Author Comment

by:karimjohnson
ID: 33604569
ok, i'm close. can you look at this file and see why the variables are not accessible to the Userform1 subroutine cmdOK_click? I'm sure it is something simple.
Doc3.doc
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 33605643
variables scoped to the checkbox routine are not available to the ok sub.

I redeclare them here as global and therefore it works:

Chris
Public price As String

Public license As String



Public Sub CheckBox1_Click()



    Load UserForm2

    UserForm2.Show

    price = UserForm2.TextBox1.Text

    license = UserForm2.TextBox2.Text

    Unload UserForm2



MsgBox prompt:="Price: " & price, buttons:=vbOKOnly

MsgBox prompt:="License: " & license, buttons:=vbOKOnly



End Sub



Public Sub cmdOK_Click()



MsgBox prompt:="Price: " & price, buttons:=vbOKOnly

MsgBox prompt:="License: " & license, buttons:=vbOKOnly



Unload Me

End Sub



Private Sub cmdCancel_Click()

Unload Me

End Sub



Private Sub UserForm_Click()



End Sub

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now