Passing a variable from a UserForm to a Procedure without Unloading

[GeeWhiz7]

A very interesting post a while back from user Herve and replies by Jon Peltier and Richie(UK) talked about initializing a userform from a general module but keeping it hidden so the value could be passed back to the general module.

Passing a variable from a UserForm to a Procedure

Hello All! I do not succeed to pass a variable (Control Button) from a UserForm to a Procedure (in a regular Module). I am declaring the variable as Public and Static, but I get a message that VBA does not support this kind of automation. What's the best way to do that? (I currently save the...

www.mrexcel.com

How can I keep the userform displayed while doing this and display the current listbox.value in a cell in a worksheet instead of the userform unloading and disappearing?

• I would like to have the value of the listbox selection shown in a certain cell and if the selection changes so does the value in the cell.
• Once the user selects the value they want, my intention is to use another button on the userform (action below) to perform an action using the value in that cell.
• If they want to do it again with that value or another, they keep selecting and then pushing action, all the while the form is present.
• I'd like the userform to stay visible until the user pushes a dismiss or 'x' button.

Thanks for the help!

Their code which works to get the one value, but unloads the form and makes it disappear is here.

'General module:
'Code:
Sub ShowUF()
UserForm1.Show
MsgBox UserForm1.ListBox1.Value
Unload UserForm1
'unload it now that info obtained
End Sub
'Userform code:
'Code:
Private Sub CommandButton1_Click()
UserForm1.Hide
'note Hide not Unload
End Sub

Private Sub UserForm_Initialize()
With ListBox1
    .Clear
    .AddItem ("Test1")
    .AddItem ("Test2")
    .AddItem ("Test3")
End With
End Sub

 

[igold]

From your description, I think this is what you want. In the Visual Basic Editor window with the Userform selected so that its properties show in the VBE properties window, try setting the property "ShowModal" to False.

 

[GeeWhiz7]

From your description, I think this is what you want. In the Visual Basic Editor window with the Userform selected so that its properties show in the VBE properties window, try setting the property "ShowModal" to False.

Hi igold,
Thanks for the suggestion. In this case the modeless doesn’t solve it.

The way the code looks to me and I’m a novice for sure, is that the form is initialized but is hidden so that the values that the user selects in the controls can be passed to the general module. Then the button just unloads the form when done.

I guess what I haven’t tested is whether as the selection changes in the form while open the variable changes too. Kind of a duh moment now for me. Need to see if that works.

What I think I like about this is that it avoids having a class module event controller at all and allows the variables from the object controls on the form to be easily accessed. Let me try what I’m thinking and I’ll pass back what I find.

 

[igold]

TBH, I did not read the code or look at the link- I stupidly just assumed. Now that I am looking at your code, and ran it on a test book with a form. If you remove (or comment out) the indicated line, wont the form stay open for you. I am sure there is more code that I am not seeing so this is really just a toss out there...

'General module:
'Code:
Sub ShowUF()
UserForm1.Show
'MsgBox UserForm1.ListBox1.Value
'Unload UserForm1
'unload it now that info obtained
End Sub
'Userform code:
'Code:
Private Sub CommandButton1_Click()
UserForm1.Hide
'note Hide not Unload
End Sub
Private Sub ListBox1_Click()
    MsgBox UserForm1.ListBox1.Value
End Sub

Private Sub UserForm_Initialize()
With ListBox1
    .Clear
    .AddItem ("Test1")
    .AddItem ("Test2")
    .AddItem ("Test3")
End With
End Sub

 

[GWteB]

Hi there,

I would implement a so called "call back". The provided approach below takes advantage of the fact a userform's module is a (special type of) class module. An additional, custom made property of the userform's object is introduced here, named ListBoxValue. This property then can be read by other code in other modules as long as the userform is "alive" (i.e. available in the computer's memory), regardless whether the userform is displayed or not.

Note that the "built-in" Show method of the userform is used in the form itself. Furthermore, the userform's code is just taking care of the things that are happening on/with the userform itself. That way the code of the userform can kept clean and clear. Actions that take place outside the form (e.g. changes to a worksheet) can be coded in other (standard) modules, the "call back" procedures.

This way the code can be easily adapted or expanded in case of changing needs. See if this works for you.

This goes in a standard module:

Public Sub ShowForm()
    Dim MyForm As UserForm1
    Set MyForm = New UserForm1
    ' note that the custom made ShowDialog method is used!
    MyForm.ShowDialog ' could be vbModeless as well
End Sub

Public Sub DlgOnAction(ByVal argForm As UserForm1)
    ' this pocedure is invoked by the userform
    ' it therefore needs to be declared Public
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = argForm.ListBoxValue
End Sub

This goes in the userform module:

Option Explicit

Private Type TLocals_UserForm1
    ListBoxValue    As String
End Type
Private This As TLocals_UserForm1

' -- Control Events --

Private Sub BtnAction_Click()
    ' invoke sub in standard module
    DlgOnAction Me
End Sub

Private Sub BtnDismiss_Click()
    OnQuit
End Sub

Private Sub ListBox1_Change()
    This.ListBoxValue = Me.ListBox1.Value
End Sub


' -- Private Methods --

Private Sub OnQuit()
    Me.Hide
    Unload Me
End Sub

Private Sub Init()
    With Me.ListBox1
        .Clear
        .AddItem ("Test1")
        .AddItem ("Test2")
        .AddItem ("Test3")
    End With
End Sub


' -- Public Methods --

Public Sub ShowDialog(Optional ByVal argShowType As VBA.FormShowConstants = vbModal)
    Init
    Me.Show argShowType
End Sub


' -- Public Properties --

Public Property Get ListBoxValue() As String
    ListBoxValue = This.ListBoxValue
End Property

 

[GeeWhiz7]

TBH, I did not read the code or look at the link- I stupidly just assumed. Now that I am looking at your code, and ran it on a test book with a form. If you remove (or comment out) the indicated line, wont the form stay open for you. I am sure there is more code that I am not seeing so this is really just a toss out there...

'General module:
'Code:
Sub ShowUF()
UserForm1.Show
'MsgBox UserForm1.ListBox1.Value
'Unload UserForm1
'unload it now that info obtained
End Sub
'Userform code:
'Code:
Private Sub CommandButton1_Click()
UserForm1.Hide
'note Hide not Unload
End Sub
Private Sub ListBox1_Click()
    MsgBox UserForm1.ListBox1.Value
End Sub

Private Sub UserForm_Initialize()
With ListBox1
    .Clear
    .AddItem ("Test1")
    .AddItem ("Test2")
    .AddItem ("Test3")
End With
End Sub

No worries...half the time when I read the code, I'm still pretty lost. It does stay open that way so that is helpful.

 

[GeeWhiz7]

Hi there,

I would implement a so called "call back". The provided approach below takes advantage of the fact a userform's module is a (special type of) class module. An additional, custom made property of the userform's object is introduced here, named ListBoxValue. This property then can be read by other code in other modules as long as the userform is "alive" (i.e. available in the computer's memory), regardless whether the userform is displayed or not.

Note that the "built-in" Show method of the userform is used in the form itself. Furthermore, the userform's code is just taking care of the things that are happening on/with the userform itself. That way the code of the userform can kept clean and clear. Actions that take place outside the form (e.g. changes to a worksheet) can be coded in other (standard) modules, the "call back" procedures.

This way the code can be easily adapted or expanded in case of changing needs. See if this works for you.

This goes in a standard module:

Public Sub ShowForm()
    Dim MyForm As UserForm1
    Set MyForm = New UserForm1
    ' note that the custom made ShowDialog method is used!
    MyForm.ShowDialog ' could be vbModeless as well
End Sub

Public Sub DlgOnAction(ByVal argForm As UserForm1)
    ' this pocedure is invoked by the userform
    ' it therefore needs to be declared Public
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = argForm.ListBoxValue
End Sub

This goes in the userform module:

Option Explicit

Private Type TLocals_UserForm1
    ListBoxValue    As String
End Type
Private This As TLocals_UserForm1

' -- Control Events --

Private Sub BtnAction_Click()
    ' invoke sub in standard module
    DlgOnAction Me
End Sub

Private Sub BtnDismiss_Click()
    OnQuit
End Sub

Private Sub ListBox1_Change()
    This.ListBoxValue = Me.ListBox1.Value
End Sub


' -- Private Methods --

Private Sub OnQuit()
    Me.Hide
    Unload Me
End Sub

Private Sub Init()
    With Me.ListBox1
        .Clear
        .AddItem ("Test1")
        .AddItem ("Test2")
        .AddItem ("Test3")
    End With
End Sub


' -- Public Methods --

Public Sub ShowDialog(Optional ByVal argShowType As VBA.FormShowConstants = vbModal)
    Init
    Me.Show argShowType
End Sub


' -- Public Properties --

Public Property Get ListBoxValue() As String
    ListBoxValue = This.ListBoxValue
End Property

Thanks GWteB, this is starting to make a little more sense to me. It does setup public properties, but I think I understand what it is doing. What I'm still lost about is how to access the value that is inside the ListBoxValue property. All this userform stuff is new to me so there is that issue too.

I tried calling another sub (called test) from the action button control event, but I think I'm misunderstanding the relationships and the message box ends up blank. Watching or looking at immediate window doesn't show anything in there when I select "Test2".

' -- Control Events --

Private Sub BtnAction_Click()
    ' invoke sub in standard module
        
    Call Test
        
    DlgOnAction Me
End Sub

Sub Test()
Dim x As String

x = ListBoxValue

MsgBox x

End Sub

 

[EXCEL MAX]

Here is simple example that may help you to easier understand.
This goes to the UserForm1 module.

Private Sub btnAction_Click()
   MsgBox "Do some action with " & Range("A1")
End Sub

Private Sub btnDismiss_Click()
   Me.Hide
End Sub

Private Sub ListBox1_Change()
   Range("A1") = ListBox1.Value
   vListboxValue = Range("A1").Value
End Sub

Private Sub UserForm_Initialize()
   With ListBox1
       .Clear
       .AddItem ("Test1")
       .AddItem ("Test2")
       .AddItem ("Test3")
   End With
   Range("A1") = UserForm1.ListBox1.Value
   vListboxValue = Range("A1").Value
End Sub

This goes to the standard module
Call this procedure from button on the sheet.

Public vListboxValue

Sub ShowUF()
   UserForm1.Show
   Unload UserForm1
End Sub

 

[GeeWhiz7]

Here is simple example that may help you to easier understand.
This goes to the UserForm1 module.

Private Sub btnAction_Click()
   MsgBox "Do some action with " & Range("A1")
End Sub

Private Sub btnDismiss_Click()
   Me.Hide
End Sub

Private Sub ListBox1_Change()
   Range("A1") = ListBox1.Value
   vListboxValue = Range("A1").Value
End Sub

Private Sub UserForm_Initialize()
   With ListBox1
       .Clear
       .AddItem ("Test1")
       .AddItem ("Test2")
       .AddItem ("Test3")
   End With
   Range("A1") = UserForm1.ListBox1.Value
   vListboxValue = Range("A1").Value
End Sub

This goes to the standard module
Call this procedure from button on the sheet.

Public vListboxValue

Sub ShowUF()
   UserForm1.Show
   Unload UserForm1
End Sub

Thank you EXCEL MAX, this is the explanation I needed to understand it better!

Steps if I'm understanding correctly:

• Worksheet Button Click calls the ShowUF sub.
  • Form shows up and apparently has something already built-in so it doesn't go straight to Unload...guess it only does that if I hit 'x'.
  • Once shown, the form is initialized and the listbox1 items added and it waits for a change in the Listbox1
• When something is clicked in Listbox1, the ListBox1_change sub is called and
  • This is where you grab the variable value to assign to cell A1!
  • Then assign that value to public variable vListboxValue.
• Action button uses the A1 value, but I suppose could also use the vListboxValue public variable.
• Dismiss Hides the form, but also clears the vListboxValue value.
• Hitting 'x' unloads the sheet also clearing the vListboxValue.

 

[GWteB]

I realy had to peek to F1 at GP Canada so a delayed response.

What I'm still lost about is how to access the value that is inside the ListBoxValue property.

I'll try to explain.

As of our post #5 code, we know there's a custom made ListBoxValue property, which is a member of the UserForm1 object*.
VBA has some built-in implicitness** and when we are trying to "reach" a member of a certain object without being explicit about that certain (parent) object, VBA doesn't recognize that member and treats the given name (in this case ListBoxValue) as a (not yet declared!) variable of the Variant type (and declares it for you). That's what's happening in your Test sub.
If you would have used the Option Explicit directive, VBA would have warned you about a not existing variabele at compile time. Now you end up with an empty ListBoxValue variable.

The DlgOnAction procedure, which is invoked by the Button_Click event handler in your userform, is used to perform the desired action, see attached.
This procedure needs an argument to be passed on (red rectangle) of type UserForm1 (your userform). That argument is used to "reach" to its member named ListBoxValue (blue rectangle).
This DlgOnAction procedure can be expanded with code to perform more actions if required.
Your userform can be expanded with more custom made properties, which will be reachable with this approach.

Finally, unlike both the code you refered to in your post #1 and @EXCEL MAX's code, my code is not sensitive to the modality of the userform.
If you don't want the user to do anything else then interacting with your form as long as it's displayed, you can show it modal. However, if users are allowed to interact with worksheets as well at the same time the form is displayed, you can show the form modeless without the need to modify the code in a drastic way.

Hopefully this is more clear.

* For better understanding, in Excel and VBA it's all about Objects, which may have members. Those members come in three types: Methods, Properties, and Events (more details over here).
** More info about VBA's implicitness:

Implicit and Explicit declaration of variables in VBA

VBA allows a great deal of flexibility in declaring variable. Many time this flexibility leads to poor quality of coding. In this article I am going to guide you what kind of practices you should embrace while declaring a variable in VBA. This is a guidance to make you perfect in variable...

learnexcelmacro.com

VBA Data Types - Converting - Implicit

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com