Visual Basic Editor (vba) Sample Forms Project Excel For Mac

The rest of this chapter requires VBA (visual basic applications). It covers essential things you must do in Excel 2011 to make full use of dialog sheets. Running a dialog sheet in Excel 2011 for Mac.

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.

Visual Basic Editor (vba) Sample Forms Project Excel For Mac

1. Open the Visual Basic Editor.

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

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:

Resume Sample Forms

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:

Where is Visual Basic Editor in Microsoft Excel 2007, 2010, 2013, 2016, 2019 and 365?

Seek for Visual Basic Editor quickly if you have Classic Menu for Office

If you have installed Classic Menu for Office on our computer, you can find out the Visual Basic Editor in a familiar user interface of Microsoft Excel 2003/XP(2002)/2000:

• Click the Tools drop down menu under Menus tab;
• Click the Control item;
• Continue to click the View Code item, and then you will view the Visual Basic Editor Window pops up.

Figure 1: Open Visual Basic Editor from Classic Menu

Click the View Code item, and then you will view the Visual Basic Editor Window pops up.

Figure 2: Visual Basic Editor

Find out Visual Basic Editor in Ribbon if you do not have Classic Menu for Office

In Microsoft Excel 2007, 2010, 2013, 2016, 2019 and 365 Ribbon, it is hard to find out the Visual Basic Editor command. That’s because it is moved into Developer tab, which is not in Ribbon:

• Firstly of all you need to add Developer Tab into Microsoft Excel 2007/2010/2013/2016/2019 Ribbon. Here are two tips:
  How to add Developer Tab into Excel 2007 Ribbon?
  How to add Developer Tab into Excel 2010/2013/2016/2019 Ribbon?
• Click the Developer tab;
• Go to the Code group;
• Then you will view the Visual Basic button. Click it, and you will enter the Visual Basic Editor window.

Figure 3: Open Visual Basic Editor from Ribbon

More Tips for Microsoft Excel 2007, 2010, 2013, 2016, 2019 and 365 (includes Office 365)

Classic Menu for Office

Free Sample Forms Letters

Brings the familiar classic menus and toolbars back to Microsoft Office 2007, 2010, 2013, 2016, 2019 and 365. You can use Office 2007/2010/2013/2016 immediately without any training. Supports all languages, and all new commands of 2007, 2010, 2013, 2016, 2019 and 365 have been added into the classic interface.

Screen Shot of Classic Menu for Excel