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

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.
Who is Participating?
David LeeConnect With a Mentor Commented:
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
   '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
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?", _

          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

              Unload Me
          End If

End Sub
LindaWestonAuthor Commented:
Thanks very much for these comments.  I'll see what I can do and then post back later.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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! ;)
David LeeCommented:
Points should go to the posts used to solve the problem.  
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!
David LeeCommented:
You're welcome.  Always happy to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.