Create an Excel UserForm, Part 2 of 3

Creating a simple Excel User Form

Get a jump start on Excel VBA with the following book available now at Amazon.com!

Scroll Bars - Financial Modelling

What do you do when you are given a set of targets to meet with a forecast that is not quite meeting them?  You may have several variables involved and you need to find what affect each change has to your model.  As they say, a picture is worth a thousand words, and when dealing with data it could be no further from the truth.

---

Enter the scroll bars pared with charting (click on image for full view):

Did you notice how the chart to the lower right is impacted by the change in variables?

---

For the user, you simply one or more of the scroll bars up/down to increase or reduce these input values to see (in real time) the impact within the chart to the lower right.  To create these scroll bars, you need to follow these 5 simple steps:

1. From the developer ribbon select insert then choose the following:
2. From the location you wish to locate this control click and drag to the size you wish this to be.
3. Right click and select format control.
4. From the control tab you can set your minimum and maximum values for this control and by what increment each click will change it.
5. In cell link choose the destination for the value present in the scroll bar.

---

For a more about modeling with Excel 2007

 

Scenario Analysis in Excel

Buttons that Pop-Up

Create Pop Up User Message Boxes

This clip demonstrates how easy it is to generate pop-up windows with buttons where you can incorporate user feedback as variables within your code.  Using these options allow you to expand your macros beyond simply automating repetitive tasks to incorporating differing scenarios to drive what macro or function to use next.