How to pass a variable from a command button action on a user form to a module in Excel VBA

Question

I am new to the board. I have a module in VBA for Excel and an associated user form with 4 CommandButtons. I call the user form with frmSelect.Show. The user is to pick on 1 of the 4 Command buttons and then a value is assigned to a variable that I want to pass to the module. This way I can tell which CommandButton was activated. I cannot seem to figure out how to pass a variable as the variable always comes back to the module as a null (0).

This is the module code:

Sub BumpGenerator()
Dim Pattern As Integer
frmSelect.Show
If Pattern = 1 then 
    Do some stuff
End If
If Pattern = 2 then 
    Do some other stuff
End If
If Pattern = 3 then 
    Do some other stuff
End If
If Pattern = 4 then
    Do this stuff
End If
End Sub

This is the code in the user form:

Private Sub CommandButton1_Click()
Pattern = 1
frmSelect.Hide
End Sub

Private Sub CommandButton2_Click()
Pattern = 2
frmSelect.Hide
End Sub

Private Sub CommandButton3_Click()
Pattern = 3
frmSelect.Hide
End Sub

Private Sub CommandButton4_Click()
Pattern = 4
frmSelect.Hide
End Sub

I have tried using:

1. 'Public Pattern As Integer' above my module

2. Passing Pattern as a variable using 'BumpGenerator(Pattern As Integer)'

3. Using 'Call BumpGenerator(Pattern)' in the user form

4. Using 'BumpGenerator Value:=Pattern'

but none of those options changed my null.

Thank you for any replies

Answer 1

Forms are just a special type of class so you can do with forms anything you can do with a class.

In your userform define a UDT which holds the values of you module level variables.

Private Type State
    Pattern as Long
End Type

Private s   as State

Private Sub CommandButton1_Click()
    s.Pattern = 1
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    s.Pattern = 2
    Me.Hide
End Sub

Private Sub CommandButton3_Click()
    s.Pattern = 3
    Me.Hide
End Sub

Private Sub CommandButton4_Click()
    s.Pattern = 4
    Me.Hide
End Sub

Public Property Get Pattern() as long
    Pattern = s.Pattern
End Property

' and then in your module
Sub BumpGenerator()

    frmSelect.Show

    ' You don't lose access to the form just because you've hidden it.
    Select Case frmSelect.Pattern  
        Case 1
            Do some stuff

        Case 2 
            Do some other stuff
     
        Case 3 
            Do some other stuff

        Case 4 
            Do this stuff

     End Select
End Sub

By the way, you should be aware that you are making a classic newbie mistake as you are using the default instance of frmSelect rather than creating a specific instance of the form. This is why you can use frmSelect.Hide rather than Me.Hide.

Its much better (in the longer run) to create your own instances of forms.

Dim mySelect as frmSelect
Set mySelect = New frmSelect
etc....

I'd also suggest that you install the free and fantastic Rubberduck addin for VBA and pay attention to the Code Inspections

Answer 2

If you declare the variable Pattern global than your code works. I would also recommend to have a look at the scoping rules of vba.

1. User-Form modul

Option Explicit

Private Sub CommandButton1_Click()
    Pattern = 1
    BumpGenerator
End Sub

Private Sub CommandButton2_Click()
    Pattern = 2
    BumpGenerator
End Sub

Private Sub CommandButton3_Click()
    Pattern = 3
    BumpGenerator
End Sub

Private Sub CommandButton4_Click()
    Pattern = 4
    BumpGenerator
End Sub

2. Standard modul

Option Explicit

Global Pattern As Integer

Sub BumpGenerator()

    Debug.Print Pattern
    frmselect.Hide

End Sub