Free Excel Training - 10 Lessons (Over 100 pages) This is a Free Excel Training with 10 different lessons. The lessons teaches the fundamentals of Excel like Cut/copy/paste, Custom formatting, formulas, useful functions & the insert function, calculations, effective printing, data sorting, autoformats, creating a charting spreadsheet, password protection, the if function and nesting. How to make Login Form in Excel and VBA. Transfer Data from Microsoft Excel to Google Sheet. Showing Multiple Lists in a Single ListBox Dynamically. Create a Dynamic Map Chart in any version of Excel. Use Live Excel Charts as a Tooltip on Mouse Hover. UserForm Events in VBA. Time and Motion Tracker. UserForm and Multiple Option Buttons in VBA.

Add the Controls | Show the Userform | Assign the Macros | Test the Userform

This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows:

Add the Controls

To add the controls to the Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.

4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.

Note: a combo box is a drop-down list from where a user can select an item or fill in his/her own choice. Only one of the option buttons can be selected.

Show the Userform

To show the Userform, place a command button on your worksheet and add the following code line:

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

1. Open the Visual Basic Editor.

2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code.

3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

4. Add the following code lines:

Explanation: text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.

Assign the Macros

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

1. Open the Visual Basic Editor.

2. In the Project Explorer, double click on DinnerPlannerUserForm.

Excel Vba Userform Templates Downloads Download

3. Double click on the Money spin button.

4. Add the following code line:

Explanation: this code line updates the text box when you use the spin button.

5. Double click on the OK button.

6. Add the following code lines:

Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.

7. Double click on the Clear button.

8. Add the following code line:

Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.

9. Double click on the Cancel Button.

10. Add the following code line:

Explanation: this code line closes the Userform when you click on the Cancel button.

Test the Userform

Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Result:

From this page you can download Excel spreadsheets with VBA macro examples.

The files are zip-compressed, and you unzip by right-clicking (once the file is downloaded) and choose 'Unpack' or whatever Windows suggests.

The spreadsheets exemplify some of the things I write about on this site, and to the right of each download link is a (www)-link that will take you to the corresponding webpage.

Once you have opened an Excel workbook, you can open the Visual Basic editor by pressing ALT+F11. I do not have a certificate, so you will probably need to select a low security level to run the macros.

Excel Vba Userform Templates Downloads

The examples have all been made in Excel 2000 or 2003 (Danish version), and if they don't work in other versions it may be, that I have made mistakes, but it could also be a compatibility issue.

Excel Vba Userform Templates Download

Automation error

Excel Vba Userform Templates Downloads Free

Excel 2016 introduced a new bug: You get an error message, 'Automation error', when you open (some) spreadsheets with macros made with Excel 2003 or older.

There are no problems running the macros, but the message is annoying. To make it disappear just save as a macro enabled workbook in the new format (*.xlsm).

Excel Vba Userform Templates Downloads Pdf

Downloads: