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:
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:
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.
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., Copyright © 2016. All rights reserved.