How to pass public variable value from user form command button to other module

z3115

Board Regular
Joined
Nov 1, 2013
Messages
71
I have a macro that calls a user form. When the user clicks the "ok" button on the user form, a command-button-click macro runs, which sets a global variable ("formVal" to the value of one of the form fields. All that works well. My issue is when I then run a macro in a different module (but same workbook) the global variable is null.

Here is the code in the user form. "msgboxtest" works when placed on the userform code page, but when I move it to a module it shows that the variable is null. (I also have "Option Explicit Public formVal as String on the module code page)

Code:
Option Explicit
Public formVal As String

Private Sub CommandButton1_Click()
    formVal = ComboBox1.Text
    SGF.Hide '(SGF is the name of the user form)
    Call msgboxtest
End Sub


Sub msgboxtest()
     'This works fine when I have it one the same page as the user form code, but when I move it to a module it says the variable is null
     MsgBox formVal
End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
This line
Code:
Public formVal As String
needs to go in a standard module, rather the the UserForm module
 
Upvote 0
This line
Code:
Public formVal As String
needs to go in a standard module, rather the the UserForm module

I have the "Public formVal As String" in the standard module as well, at the very top of the module page.
 
Upvote 0
It should only be declared once in any workbook, so remove it from the userform module
 
Upvote 0
It should only be declared once in any workbook, so remove it from the userform module

That worked, thank you so much! If you don't mind, can you explain why it didn't work when it was only in the user form? Thanks again!
 
Upvote 0
You're welcome & thanks for the feedback
Any public variables declared in Sheet modules, ThisWorkbook module, or userform modules are only available to code in that module.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top