Property Procedures - Passing Information to and from Forms

A userform is a convenient, professional-looking interface between a VBA procedure and the user. However, you might have trouble moving information from the VBA procedure to the form, or retrieving information when the user has finished with the form.

You may have used global variables or saved data on an empty worksheet, but this is not always a safe approach. For example, if you replace a form's combobox with option buttons, the VBA procedure will break, because it is looking for a combobox that's been deleted.

The "official" way to pass information between a form and a procedure is to create properties for the form using property procedures. Property procedures are handy because they can:

• set the values of variables,
• change objects on the form, and
• perform other actions whenever a property is accessed.

Properties also allow more complete encapsulation of the form; the main procedure doesn't care what technique is used in the form, all it needs is the name of the property. If the form is redesigned, it will still work as long as the properties themselves are the same. Properties are called using code like UserForm1.Property, the way properties of any VBA object are accessed (like ActiveSheet.Name).

How about a simple example? Start with a simple userform, FYesOrNo, with two option buttons (optYes and optNo, with captions "Yes" and "No") and a commandbutton (cmdFinished, "Finished"). The command button hides the form, but doesn't unload it, so the form and its controls are still in memory.

Here is the code behind the form:

' UserForm FYesOrNo Code Module

Option Explicit

Public Property Get YesOrNo() As String
  Select Case True
    Case Me.optYes.Value
      YesOrNo = "Yes"
    Case Me.optNo.Value
      YesOrNo = "No"
  End Select
End Property

Public Property Let YesOrNo(ByVal sNewValue As String)
  If UCase$(sNewValue) = "NO" Then
    Me.optNo.Value = True
  Else
    Me.optYes.Value = True
  End If
End Property

Private Sub cmdFinished_Click()
  Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    ' user clicked the X button
    ' cancel unloading the form, use close button procedure instead
    Cancel = True
    cmdFinished_Click
  End If
End Sub

Property Let means the procedure can assign a value to the property by saying, e.g., UserForm1.YesOrNo = "Yes". When the property is assigned in this way, the code within the Let procedure is run, which in this case, selects the appropriate option button. This is how a program might preload a form with default values, or preferences already specified by a user (aisle seat or window, compact or midsize).

Property Get is how the procedure retrieves the value of the property, using UserForm1.YesOrNo to run the Get procedure, which in this case returns Yes or No based on which option button is selected. This is how a program gets important selections from the form, like which airline flight to book, or whether the traveler needs a rental car.

Tip in a tip. The UserForm_QueryClose procedure answers another common question, how to prevent the user from closing the form using the X close button in the top right corner. Without this procedure, using the X would cause an error, because the form would be unloaded before the regular procedure could get the information back. Whenever the form is closing, the QueryClose event occurs and this procedure runs. If the value of CloseMode indicates that the user has clicked the button, this procedure cancels the form closing. Rather than give the user an annoying message to click the proper button, the procedure instead runs the code that the proper button click would have run.

Here's the code within a simple procedure in a regular code module. It first asks a Yes or No question, instantiates the form, sends the preliminary response to the form and shows the form. When the form is hidden, it asks the form what the final answer is, and displays that.

Option Explicit

' Regular procedure in a regular code module
Sub Test()
  Dim lResponse As Long   ' pass to form
  Dim sResponse As String  ' get from form
  Dim frmYesOrNo As FYesOrNo

  'ask user for initial setting
  lResponse = MsgBox("Yes or No", vbQuestion + vbYesNo)
  
  ' turn setting into a string
  sResponse = IIf(lResponse = vbYes, "Yes", "No")

  ' start up the form
  Set frmYesOrNo = New FYesOrNo
  With frmYesOrNo
    ' pass variable to form
    .YesOrNo = sResponse

    ' show the form
    .Show

    ' get new value back from the form
    sResponse = .YesOrNo

  End With

  ' got the information, now close the form
  Unload frmYesOrNo

  ' tell user what new value is
  MsgBox "Form returned " & sResponse, vbExclamation

End Sub

Run the Test procedure in the regular module. First a message box asks whether the answer is Yes or No. A practical example is more likely to get the initial value from a database or other stored values.

The form then appears, and the option button corresponding to the message box response is selected. The form is populated with initial values or preferences.

After the form is cleared, a second message box shows the user what the final answer was. The practical application would be forwarding the information to procedures which book a reservation and bill the credit card.

Thanks to Debra Dalgleish for her very constructive suggestions.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | PTS Blog | Training | Charts and Tutorials

Peltier Technical Services, Inc., Copyright © 2016. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile