MgtOp-556-Topic-1-Set-5 - MgtOp 556Advanced Business...

Info icon This preview shows pages 1–6. Sign up to view the full content.

MgtOp 556—Advanced Business Modeling Professor Munson Topic 1 VBA Programming in Excel for Decision Support Systems (Set 5—User Forms: Becoming a True “Windows Programmer”) “Working with user forms is arguably the most fun part of VBA application development. You can use your creative and artistic talents to design the dialog boxes that user interact with.” Albright, S. Christian, VBA for Modelers, 5 th Ed., Cengage Learning, 2016, p. 231
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Creating Dialog Boxes (User Forms) Input boxes, message boxes, Yes-No boxes, and form controls (e.g., list boxes, spin buttons, etc.) work well for specific tasks, but they are limited by basically being able to perform only one operation. Many practical applications, however, necessitate “dialog boxes” that may require the user to enter several different responses into the same box. Programmers can employ “user forms” to accommodate such situations. These are the primary means for getting users’ inputs. Two Parts to Creating User Forms 1. Design the form For the required functionality and attractiveness Insert a user form Choose and place controls Set control properties 2. Write Event Handlers To appropriately respond to user actions UserForm_Initialize Code btnCancel_Click Code btnOK_Click Code Code for any other controls
Image of page 2
Designing User Forms Getting Started 1. Project Explorer and Properties Window should be visible (use the View tab if they’re not). 2. →Insert→UserForm (A blank form will appear with the Toolbox)
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Available Controls Pointer (put the cursor in ready mode) Label (for explanations and prompts—no user input) TextBox (user types something in the box) ComboBox (user chooses from a list or types in) ListBox (user chooses one or more items from a list) OptionButton (user selects one of several ToggleButton (either options) depressed or not) CheckBox (user selects Frame (groups related any of several options) controls—especially option buttons) TabStrip (view different sets of information for CommandButton (runs related controls) subs) Image (displays a picture) MultiPage (presents multiple screens of info as a SpinButton (increments and single set) decrements numbers) ScrollBar (returns or sets the value of another control based on the position of the scroll box) RefEdit (similar to a TextBox control, but used specifically to let the user select a worksheet range) Note: Many other controls can be added to the Toolbox. To see them, right-click on any of the controls in the tool box, and select →Additional Controls… . A useful one is Microsoft Date and Time Picker Control 6.0 (SP6) , which lets you put a date box on the form where the user selects the date from a calendar.
Image of page 4
Adding Controls to the User Form To add any control, click it in the Toolbox and then drag it onto the form.
Image of page 5

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 6
This is the end of the preview. Sign up to access the rest of the document.
  • Spring '16
  • C.Munson
  • event handler, Private Sub, User Form

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern