When to write code in a module or within the code for the user form?

[kb24gunner10]

I have several questions here that I was hoping you guys could help me with.

1. I have a user form that captures the response of the user when they click okay and then does some actions in the excel sheet based on the response. What is the difference between writing the code related to the actions done on the excel sheet in the form under the event handler cmdOK_Click() versus writing it in a module (cmdOK_Click() would just set the values of variables in this case)? When would I do one over the other?

2. Are form controls private to the form module area? For example, in a module, can I refer to the value of a combo box?

3. Last, can variables that are declared in a form module be used elsewhere or would I have to declare them in a module and then set them in the form module?

By form module, I mean the form code area, don't know if I'm using the correct terminology.

Thanks.

 

[mikerickson]

2) Userform controls can be accessed from any module. The following code, when in a normal module, will set the intital value of Userform1's control ,TextBox1 and then show that user form.

Sub Test()
    Userform1.TextBox1.Text = "Testing"
    Userform1.Show
End Sub

3) Variables declared in a user form can be used elsewhere, if they are declared as Public. If this code is in the code module for Userform1. Note that the declaration of myVar is before any procedure code.

'in userform code module

Public myValue As Double

Private Sub TextBox1_Change()
    ' more code

It can be used in any module like this.

Sub test2()
    UserForm1.myValue = 5
    MsgBox UserForm1.myValue / 2
End Sub

A userform can been described as a class module with a "built-in" user interface. The coding technique above is similar to what one might find in the code module for a custom object (class module). One can use property procedures in a user form. Consider a user form with four controls, two text boxes and two command button and this code in its code module

'in userform code module

Property Get myValue() As Double
    With Me
        myValue = 10 * Val(.TextBox1.Text) + Val(.TextBox2.Text)
    End With
End Property

Property Let myValue(inValue As Double)
    With Me
        .TextBox1 = Int(inValue / 10)
        .TextBox2 = inValue Mod 10
    End With
End Property

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

That can be called by code like this, in a normal module

Sub test2()
    With UserForm1: Rem creates one instance of uf1
        .myValue = 345
        .Show
        MsgBox "first" & vbCr & .myValue & vbCr & .TextBox1.Text & ", " & .TextBox2.Text
    End With
    
    With UserForm1: Rem if hidden, same userform. if unloaded, different instance of uf1
        MsgBox "second" & vbCr & .myValue & vbCr & .TextBox1.Text & ", " & .TextBox2.Text
    End With
    
    Unload UserForm1
End Sub

The .myValue property puts "34" in textbox1 and "5" in textbox2.
Note that the the difference between hiding and unloading a user form.
Also note that there can be different instances of a user form. Compare the results when pressing the Hide button with what happens when the Unload button is pressed.

Sub test2()
    With UserForm1: Rem creates one instance of uf1
        .myValue = 345
        .Show
        MsgBox "first" & vbCr & .myValue & vbCr & .TextBox1.Text & ", " & .TextBox2.Text
    End With
    
    With UserForm1: Rem if hidden, same userform. if unloaded, different instance of uf1
        MsgBox "second" & vbCr & .myValue & vbCr & .TextBox1.Text & ", " & .TextBox2.Text
    End With
    
    Unload UserForm1
End Sub

QUESTION 1: Where to put code?
That's a question of personal style and preference.
I like to write UF code with the principles of self-contained and transferable.
In your situation, the task is to get data from the UF and put in in a cell.
"Self contained" means that the code that is necessary for the UF to function, the "what happens when the user changes a control" type code belongs in the UF code module.
If my project has the result of the UF put only in one cell, then that code would belong in the user form. This user form could be described as "Get data, put in cell"

If my project had the result in different cells in different situations, the "put in cell" code would go in a normal module (outside the UF code module). That would be a "get data" user form.

My style would be to avoid a "tell user form which cell, get data, put data in that cell" user form.

But this is all personal style. What works for you is what you should use.

I like user forms that can be exported from one project and used in another without having to remember to declare public variables in normal modules or ... other dependent outside coding.

Nomenclature: The technical term for where code resides is a "code module". A code module can be attached to an Excel object (user form, worksheet, workbook, etc), a user defended custom object (Class Module) or with no object (a normal module).
Also, the term "form" has many meanings, it can refer to a worksheet with specific cells for user entry. The term "userform" specifies that one is talking about a userform, not a worksheet that is used as a form.

 

[Jon Peltier]

1. If it makes sense to close the form after the user clicks OK, then I generally pass the information back to the procedure that called the form, and process the worksheet there.

2. Everything Mike has said is true, but inconsistent with robust programming.

Controls are not private, but you're really better off treating them as if they are. If you redesign the form, you'd prefer not to rewrite all the other code, right? For example, you may change the userform so it shows the list in a list box instead of your combo box, and you'd rather keep the calling code the same, instead of figuring out what stupid details you need to change. Especially if next week you switch to a set of option buttons. Let the form keep track internally of how it shows its information to the user, and let the calling code just set the initial value and get the final value.

You do this by writing property procedures in the form. I wrote a tutorial about this, Property Procedures - Passing Information to and from Forms.

Say your form's combo box (ComboBox1) has a list consisting of Greek letter names. I can use a property procedure to set the initial selection like this:

Public Property Let GreekLetter(sCombo As String)
  On Error Resume Next
  Me.ComboBox1.Value = sCombo
End Property

I set it like this. The property procedure takes the value "Beta" and selects it in the combobox.

UserForm1.GreekLetter = "Beta"

On Error is just in case sCombo isn't a valid selection.

I can use a property procedure to return the selected item like this:

Public Property Get GreekLetter() As String
  GreekLetter = Me.ComboBox1.Value
End Property

I call it from the calling procedure like this. The prooperty procedure gets the value from the combo box and returns it.

Dim sGrkLetter As String
sGrkLetter = UserForm1.GreekLetter

If I redesign the form, I change Me.ComboBox1.Value to Me.ListBox1.Value (or whatever I change it to), but in the calling code, sGrkLetter = UserForm1.GreekLetter stays the same.

So the entire code (so far) in the userform is:

Option Explicit

Public Property Let GreekLetter(sCombo As String)
  On Error Resume Next
  Me.ComboBox1.Value = sCombo
End Property

Public Property Get GreekLetter() As String
  GreekLetter = Me.ComboBox1.Value
End Property

' hides the form but keeps it in memory
' the calling procedure then continues to run
Private Sub CommandButton1_Click()
  Me.Hide
End Sub

' this is just my quick way to populate the combobox
Private Sub UserForm_Initialize()
  Me.ComboBox1.List = Array("Alpha", "Beta", "Gamma", "Delta", "Epsilon")
  Me.ComboBox1.Value = "Alpha"
End Sub

' this makes sure the form isn't removed from memory if the user clicks the red X
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
    CommandButton1_Click
  End If
End Sub

The calling code is here:

Option Explicit

Sub SampleUserFormCaller()
  Dim frm As UserForm1
  Dim sGrkLetter As String

  ' start with a new instance of the form
  Set frm = New UserForm1
  With frm

    ' pass in initial value (if desired)
    .GreekLetter = "Beta"

    ' display the form
    .Show

    ' get the selected value
    sGrkLetter = .GreekLetter
    
  End With
  Set frm = Nothing

  ' do something with the user's selection
  MsgBox "You selected " & sGrkLetter
  
End Sub

3. Yes, you can declare variables in the form and use them elsewhere. No you should not. You should probably avoid public variables as much as possible. One problem with them is they can be changed anywhere, and you never can be sure they were changed by the code you thought was changing them. If you explicitly pass values, you know what changed them.

Use property procedures as above to pass the values between your main code and your forms.