Passing variables from a UserForm to a module is an important part of working with VBA, mainly when you want to use user input in calculations, reports, or automation tasks. For example, you might want a user to enter a name or a number in a form, and then use that value in a procedure that runs in a module.
In this article, we’ll look at different ways to send data from a UserForm to a module in Excel VBA. Each method works in a slightly different way, depending on whether you want the value to be shared across multiple procedures, passed directly to a single Sub, or accessed later after the form is hidden. You’ll know how to choose the right method based on what your project needs by the end.
Steps to pass a variable from a UserForm to a module in VBA
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a standard module.
➤ At the top of the module, declare public variables:
Public EmployeeName As String
Public SalesValue As Double
Public TargetValue As Double➤ Open the UserForm (e.g., UserForm1) and add the following code to the Submit button:
Private Sub btnSubmit_Click()
EmployeeName = Me.txtEmployeeName.Value
SalesValue = Me.txtSales.Value
TargetValue = Me.txtTarget.Value
MsgBox "Values have been passed to the module successfully."
Unload Me
End Sub➤ In the same module, add this procedure to use the data:
Sub CalculatePerformance()
' Show the UserForm to get input
UserForm1.Show
' Validate input
If TargetValue = 0 Or SalesValue = 0 Then
MsgBox "Please enter valid numeric values for Sales and Target.", vbExclamation
Exit Sub
End If
' Calculate performance
Dim Performance As Double
Performance = (SalesValue / TargetValue) * 100
MsgBox EmployeeName & "'s performance is " & Round(Performance, 2) & "%"
End Sub➤ Return to Excel, run the UserForm, click Submit, then run CalculatePerformance.
➤ The module will read the values assigned by the UserForm and display the calculated performance in a message box.
Use a Public Variable in a Standard Module to Pass Variable from UserForm to Module
This method is one of the simplest ways to pass a variable from a UserForm to a module in VBA. You declare a variable as Public in a standard module so that it becomes accessible from anywhere in your VBA project, including forms, sheets, and other modules. The UserForm assigns a value to the variable, and the module can read or use it directly.
This approach is useful when you need to share values like user input (for example, Sales and Target) between the form and different parts of your code without passing them as parameters each time.
This is the dataset that we will be using to demonstrate the methods:
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new standard module.
➤ At the top of the module, declare a public variable:
Public EmployeeName As String
Public SalesValue As Double
Public TargetValue As Double
➤ Next, open the UserForm (for example, UserForm1) and add the following code to the Submit button:
Private Sub btnSubmit_Click()
EmployeeName = Me.txtEmployeeName.Value
SalesValue = Me.txtSales.Value
TargetValue = Me.txtTarget.Value
MsgBox "Values have been passed to the module successfully."
Unload Me
End Sub
➤ Now, in the same module where you declared the public variables, add this procedure to use the data:
Sub CalculatePerformance()
' Show the UserForm to get input
UserForm1.Show
' Check if values are entered properly
If TargetValue = 0 Or SalesValue = 0 Then
MsgBox "Please enter valid numeric values for Sales and Target.", vbExclamation
Exit Sub
End If
' Calculate performance
Dim Performance As Double
Performance = (SalesValue / TargetValue) * 100
MsgBox EmployeeName & "'s performance is " & Round(Performance, 2) & "%"
End Sub 
➧ EmployeeName, SalesValue, and TargetValue are updated through the form.
➧ The CalculatePerformance Sub uses those stored values to calculate and display the result.
➧ This method is best when you need to reuse the same data across multiple modules or macros without re-passing them each time.
After running the UserForm and clicking Submit, you can run the CalculatePerformance Sub to see the result appear in a message box.
Expose UserForm Values via Public Properties to Pass Value to Module
This method uses Public Property procedures (Property Get and optionally Property Let) to pass values from a UserForm to a standard module. Instead of directly referencing form controls, the module retrieves data in a controlled and professional way through properties.
This is a clean and reliable technique, especially when you want to maintain data encapsulation, meaning the UserForm manages its own data while still allowing the rest of your VBA project to access it safely.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Open your UserForm (for example, UserForm1) and paste this code inside it:
' Property to get Employee Name
Public Property Get EmployeeName() As String
EmployeeName = Me.txtEmployeeName.Value
End Property
' Property to get Sales value
Public Property Get SalesValue() As Double
SalesValue = Me.txtSales.Value
End Property
' Property to get Target value
Public Property Get TargetValue() As Double
TargetValue = Me.txtTarget.Value
End Property
➤ Now, go to Insert >> Module and paste the following code:
Sub ReadUserFormValues()
Dim EmpName As String
Dim SalesAmt As Double
Dim TargetAmt As Double
Dim Result As Double
' Show the form and get values through properties
UserForm1.Show
EmpName = UserForm1.EmployeeName
SalesAmt = UserForm1.SalesValue
TargetAmt = UserForm1.TargetValue
Result = (SalesAmt / TargetAmt) * 100
MsgBox EmpName & "'s performance is " & Round(Result, 2) & "%"
End Sub
➧ The module shows the form, waits for user input, and then retrieves the values through the public properties.
➧ This keeps your UserForm’s structure independent, meaning the module doesn’t need to know the names of individual controls.
➧ Once retrieved, the module calculates and displays the performance result in a message box.
After running the ReadUserFormValues procedure, Excel will show the UserForm. Once the user fills in the details and closes the form, the Sub will fetch the entered values and display the calculated performance percentage instantly.
Keep the Form Hidden and Read Values from Controls
Sometimes you want your UserForm to remain open in the background while your module reads the data entered by the user. Instead of unloading the form immediately, you can use Me.Hide to keep it in memory. This approach allows your VBA module to access values from text boxes, combo boxes, or other controls directly, without needing public variables or property procedures. It is especially useful when you want to perform calculations or updates immediately after the user submits the form but before closing it.
This method is simple and efficient for projects where you only need to temporarily hold the form’s data and read it directly. It keeps the workflow smooth because the form stays loaded, giving the module instant access to all controls. Once the data is read and processed, you can unload the form to release memory.
Steps:
➤ Open the VBA Editor by pressing Alt + F11 .
➤ Insert a new UserForm and add a TextBox called TextBox1 and a CommandButton called SubmitButton.
➤ Double-click the SubmitButton and paste the following code inside the UserForm:
Private Sub SubmitButton_Click()
' Hide the form instead of unloading it
Me.Hide
End Sub➤ Insert a new Module and add this procedure to read the value from the hidden UserForm:
Sub ReadUserFormValue()
' Show the form
UserForm1.Show
' Read the value from the TextBox
Dim UserInput As String
UserInput = UserForm1.TextBox1.Value
' Display the value in a message box
MsgBox "You entered: " & UserInput
' Unload the form to free memory
Unload UserForm1
End Sub➧ UserForm1.Show displays the form for the user to enter data.
➧ UserInput = UserForm1.TextBox1.Value reads the value typed into the TextBox and stores it in a variable in the module.
➧ MsgBox "You entered: " & UserInput demonstrates that the module successfully received the value from the UserForm.
➧ Unload UserForm1 finally removes the form from memory to free resources.
➤ Return to Excel, press Alt + F8 , select ReadUserFormValue, and click Run.
➤ Enter some text in the UserForm’s TextBox and click Submit. The module will immediately read the value, display it in a message box, and then unload the form.
Frequently Asked Questions (FAQs)
Why do I need to hide the UserForm instead of unloading it?
Hiding the form keeps it in memory so that the module can still access the values of its controls. Unloading it removes the form entirely, and any values in its controls are lost.
Can I pass multiple values from the UserForm to a module using this method?
Yes. You can read values from multiple controls like TextBoxes, ComboBoxes, or CheckBoxes, before unloading the form.
Do I need to use a global or public variable to transfer the value?
No. Using Me.Hide allows you to access the values directly from the UserForm controls without declaring additional global variables.
Can this method be used with multiple UserForms at the same time?
Yes. Each UserForm can be hidden, and its values can be read independently from the module before unloading.
Wrapping Up
Passing values from a UserForm to a module in VBA is essential when you want to collect user input dynamically and use it in other procedures. Using the method of hiding the form ensures that the data remains accessible without resorting to public variables. This approach is flexible, clean, and works well with multiple controls or complex forms. You can smoothly transfer data from your UserForm to any module procedure, improving the interactivity and automation of your Excel applications.
