We help IT Professionals succeed at work.

Pass Variable from Userform to Module.

RWayneH
RWayneH used Ask the Experts™
on
I am having issues passing variables from modules to userforms and from userforms back to modules after a ListBox1 selection has been made.  It is almost like I want to set everything as a Global.  There is something that I am not getting, like were the Dim, Public, Globals need to be set.  Looking for some guidance on how to approach using forms and modules together.

This case, I have a userform, showing a ListBox1 and after the select of an item in the ListBox, I am not able to set it as SheetSelected, it is make Ln16, does nothing?
Please advise and thanks.

Option Explicit
Public DebitAccBal As Double 'Integer
Public MoveAmountVal As Double 'Integer
Public SheetSelected As String

Private Sub ListBox1_Click()
    Dim MoveAmountVal As Double 'String
    Dim DebitAccBal As Double 'String
    Dim GoodVal As String
    Dim FromSheet As String
    Dim TransTo As String
    
    
    ActiveWorkbook.Unprotect
    ActiveSheet.Unprotect
    SheetSelected = ListBox1.Value
    If SheetSelected = "Budget Items" Or SheetSelected = "MasterPrimaryDepositAccount" Or SheetSelected = "DefaultAccountPg" Then
        Worksheets(ListBox1.Value).Visible = False
        MsgBox ("This is a reserved sheet tab, and you cannot delete by design.  Please select a different Account from List")
        
    ElseIf SheetSelected = "" Then 'no account to select or user exit without selecting
        MsgBox ("No account selected.  Either no account existed or user exit without selecting"), vbOKOnly, "Move money to another account"
        
    Else

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John KorchokProduction Manager

Commented:
To declare variables that have adequate scope, put them at the top of your main module, not on a worksheet or userform. If you declare them in the userform, they're only accessible in that form.

Author

Commented:
So were is it declared, when I want to pass it back to the module?  Or if I want to pass it from the module to the userform?

Author

Commented:
To explain a bit further, I have a variable named SheetSelectedB that is a ListBox1.Value  that I want to send back to a Module..  Do I have to rename SheetSelectedB, something else that is a Global in the UserForm code so it gets back to Mod?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
The preferred way to get and set userform values is with a Private Property as illustrated in this workbook.

Note that Global is outdated and replaced by Public. It is kept around for backward compatibility.
29167786.xlsm

Author

Commented:
Just so that I am understanding here.

Option Explicit
Private strSheet As String    'Set because it is used in the userform get Property Let and Get.

Triggers the Let Property?  This is new to me, and how does that tie to the use of Public Property Get?
Looks like it is doing something with the SheetSelected   _Click?

One other thing I noticed is that I used the ListBox1.Value instead of .Text??  Maybe that made a difference too?
John KorchokProduction Manager

Commented:
To passing variables in either direction between a userform and a module, declare the variables as Public in the module, not in the userform. It's simple and it works.

Author

Commented:
Ok applying John's method, I may have a unique scenario, because at times the ListBox1 can be blank, (does not show sheets that are already visible, however it seems to keep whatever that previous sheet selected was, so I need to somehow clear whatever SheetSelected value is.  When the ListBox1 is empty, there is an instruction to click the X in the upper righthand corner.  Applying Martins now.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It's simple and it works.
That's true, but since it's Public it leaves open the possibility that it could be used by new code for a differnt purpose somewhere else. That's why strSheet in my code is Private; it can't be set or used anywhere but in the userform.

In a Property, 'Get' gets the value of in this case the SheetSelected property. 'Let' sets the value of the property. A 'Set' property is also available instead of 'Let" if the property references an object.
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
To handle the situation where the listbox is empty change the Get to something like this:
Public Property Get SheetSelected() As String
    If SheetSelected = "" Then
        SheetSelected = "[Empty]" ' Or any other string value you like
    Else
        SheetSelected = strSheet
    End If
End Property

Open in new window

Author

Commented:
I believe I am getting this concept.  It is about how I name my variables (so they are not reused) and about keeping variables unique to a userform, strictly for use in them.  If I use both, approaches it would be a win/win.  If I happen to mix up naming variables, the userform property would work too.

From a design prospective, keeping vars private to a userform seems like a safer way to go, especially when dealing with larger more complicated workbooks, but for a simple and smaller workbooks, were there is not much depth to the process or procedures, using Public As [VarName] could be an easy way of getting by.  Thanks both for help me understand this better.

Author

Commented:
Thanks for the help today.  EXCELlent!!!
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018