Creating a User Form

1. Creating a User Form

2. A Better Interface • Our programs can use input boxes for input and write on the spreadsheet page for output • This works ok but is not very elegant or flexible • VBA gives us a way to create a custom form to use as an interface for our program • We’ll look at the Windows version first, then the Mac version

3. Windows version

4. To Get Started… Open a new workbook, go to the Developer tab, and click Visual Basic. In VBA, double click ThisWorkBook in the project window to show the code sheet, and type OptionExplicit. Then click the Insert UserForm icon, and choose UserForm

5. Things to Notice The toolbox has items you can put on the user form. If you don’t see it, click the toolbox icon (highlighted above) Your new user form. Grab the bottom corner and drag to make it bigger. Properties of the selected form or tool

6. Change the Name of the Form We will refer to the form by its name in our program. Name is the first property In the list.

7. Change the Caption Here’s the caption. The user will see it when using the form. The user will not see the name. Caption property

8. Other Properties • There are plenty of other properties to play with. You can try them out. • One fun one is the background color. Let’s set it to something other than white…

9. I made it yellow The colors are reached through the little down arrow at the right of the property line. System colors are the ones Windows is using for your system. The Palette gives you some other options. The BackColor property

10. Showing the Form • Unless you make it happen, the form won’t show up in your spreadsheet program • The line of code that makes the form show up is formName.Show • Let’s make it show up when you open the workbook, using the Workbook_Open event procedure. • Double click in the project window to get back to the ThisWorkbook project, and type the code. The close the workbook and reopen it

11. Here is the code…

12. My screen, after opening the workbook and enabling macros

13. Mac version

14. To Get Started Go to the Developer tab and then to the VBA editor. Under the InsertMenu, click Userform.

15. Controls you can add to the userform Showing it In the project The new userform Properties window showing Userform properties

16. Changing the Properties • Use the properties window to change the properties • We’ll change the name to frmExample • And we’ll make it bigger by changing the height to 450 and width to 600 • We’ll also change the caption to “Welcome” • And the backcolor to yellow

17. I couldn’t find a yellow I liked on the color wheel, so I used the “crayons” to select one

18. Here is the yellow form after I clicked OK.

19. Showing the form • Right now there is nothing that makes the form show up when you are in the workbook. • The command frmExample.Show makes it visible • We could put this in a macro that is activated by clicking a button, but let’s put it in the special Workbook_Open macro that makes it show up when you open the workbook.

20. I typed my code here I double clicked here to get the code window for this workbook

21. The code I typed Option Explicit '***************************************** ' Show form frmExample when the workbook opens '***************************************** SubWorkbook_Open() frmExample.Show End Sub

22. Save and quit… • I saved my workbook as a Macro-enabled (.xlsm) workbook and then quit Excel • The next slide shows what I got when I re-opened the workbook and enabled macros

23. caption The user form