Userforms

Stock Application 5 (1)

The above Youtube video will show you how to use VBA Userforms. Userforms are objects in Microsoft Excel 2007, Excel 2010, and Excel 2013 that have many applications.
Change to clear video quality to 1080P by clicking the gear icon above.
Mobile If you don't like the video quality of the above embedded video, then watch the video on My Youtube.

Why are VBA Userforms Useful?
1. Data presentation – Userforms present specific data to end-users in an organized manner. Not everyone understands what you're trying to say in your disorganized workbook. Make end-users see the data the way that you want it to be seen.
2. User Interface – Userforms enable end-users to make decisions and interact with your workbook. Create surveys or timesheets with Userforms.
3. VBA ActiveX Buttons – You can add ActiveX buttons and controls to your Userforms from the Toolbox in Project Explorer to create complex Userform designs.
4. Customization – Customized Userforms can be a Dashboard for your daily work tasks.

How to Insert a VBA Userform
In the Visual Basic Editor, click Insert at the top. Click Userform to add a Userform to the current workbook.

The Userform will now appear as Userform1 under the current workbook.

How to Load a Userform in Excel
I like to link my Userforms to Command buttons on the worksheets themselves.
1. Under the Developer Tab on the Ribbon, select Insert Command Button under the ActiveX controls.

2. Left click on the worksheet to draw your Command button as pictured below:

3. In Design Mode on the Developer Tab, double click on the Command button to access the subprocedure On-click Event for the Command button. In simple terminology, this subprocedure is the code that you want to execute when you left click on the Command button when you are NOT in Design Mode on the Developer tab. The code that I have pictured below Userform1.show will cause the Userform1 that you created to show when you click on the Command button that you placed on the worksheet.

How to Enable Userform Toolbox
In the Visual Basic Editor, double-click the Userform1. Click View at the Top and select Toolbox.

How to Enable Properties Window in Visual Basic Editor
Click View at the top and the Properties Window. Each control or object has its own set of properties such as background color, font size, and source data location. You can change the name of your objects here. Click on Userform1 and change the Name on the Properties Window to Userform2. Now when referencing this object in code it must be referenced as Userform2.

The below image contains all the buttons for the toolbox:

Toolbox Buttons Explanation

The various controls in the Toolbox can be placed on Userforms in Excel.

1. Select
Use select to highlight multiple controls on a Userform. I normally select the control if I want to change it's name, color, or font size under the properties window. I also double-click on controls to modify the subprocedures associated with that control when its Click Event happens. The below image shows what happens if you double-click on a Command button that's been placed on a Userform. I put Unload Me. Unload Me is another way to close Userforms. If you click this Command button, it will close the Userform.

2. Label
Labels are text on Userforms.
3. TextBox
TextBoxes are less useful than Listboxes mentioned below.
4. ComboBox
ComboBox is a drop-down box that can have multiple selections as its value. I use it to basically filter Userforms.
5. Listbox
A ListBox can contain a value or array of values.
6. Checkbox
Checkboxes are checkboxes.
7. OptionButton
Same as checkboxes. Watch the Youtube video to find out how to interact with Checkboxes and OptionButtons.
8. MultiPage
MultiPage lets you create multiple pages on a Userform. I would make one page a set of addresses and phone numbers and the next page the person's relatives/etc.
9. ScrollBar
ScrollBars are useful for working with a range of values that can change at specific intervals.
10. Image
Images can be embedded on Userforms. They look fancy. They can contain company watermarks or formulas if you need to reference an image. Watch the video to find out how.