How do I pass data input on userform back to calling module?

Hi
I hope someone can help me.  I have created (or more aptly, cobbled together!) a macro in Outlook that, having done various things then calls a user form for the user to enter data.  What I'm now stuck on is how to pass that collected data back into the calling macro to do more stuff with.  I have tried to find a solution to what must be a basic VB activity, but have failed miserably so far.  Could someone please explain in simple terms the process required?

Many thanks.
LindaWestonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
Hi, LindaWeston.

Create public variables to hold the data the user form needs to pass back to the calling macro.  Before destroying the userform have it set the values into the public variables.  Something like this

Public varA, varB, varC

Sub YourMacro()
   dim frmX as New UserFormX
   frmX.Show
   'Code that does something with varA'
   'Code that does something with varB'
End Sub

'Code in the form'
varA = SomeValue from the form
varB = SomeOtherValue from the form
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
javaftperCommented:
manipulating/utilising data from VBA forms is relatively easy once you know what you're doing with the object model.
there are two main things you will need to do-
1.  Set a trigger event for when you want to collect the data from the form (for example when they click on OK button).
2.  Retrieve the data from the relevant text boxes, combo boxes etc.

Without having more information it is difficult to provide an exact solution but you should be able to take these two key areas from the following example-

 Private Sub CommandButton1_Click()
          Dim name As String
          Dim company As String
          Dim status As String

          response = MsgBox("Do you want to add this name, company and status?", _
              vbYesNo)

          If response = vbYes Then
              name = TextBox1.Text
              company = TextBox2.Text
              status = TextBox3.Text

         'comment- do whatever you want with the data you have collected here

          Else
              Unload Me
          End If

End Sub
0
LindaWestonAuthor Commented:
Thanks very much for these comments.  I'll see what I can do and then post back later.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

LindaWestonAuthor Commented:
Hi, well I tried BlueDevilFan's information first as it seemed simpler and this worked perfectly for me, so I didn't need to try the info from javaftper.  Therefore what is the fair way to award points here? I don't want to upset anyone as I might need more help soon! ;)
0
David LeeCommented:
Points should go to the posts used to solve the problem.  
0
LindaWestonAuthor Commented:
Thanks for the guidance.  And thanks very much for the solution. I have learnt a lot from this - particularly from the words 'Before destroying the userform ....'  - I hadn't thought of it like that before and now a lot more makes sense!
0
David LeeCommented:
You're welcome.  Always happy to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.