Not too long ago, I decided it was time to evolve my mode of transportation. Sure, I had the tools of the trade- shoes, bicycle, subway pass. But I desired something more, something greater than myself which would weigh over 2 tons. Yes, my friends- I was looking for a new car.
Being a car buyer is quite stressful especially if you have limited experience. Although some of you may believe the process of purchasing a vehicle is an artistic notion, I decided I wanted to consider just a few factors: price, engine, miles per gallon, passengers, automatic/manual transmission.
Research is very important for consumers. The first item that I wanted to determine in my new quest for a new mode of transportation is, what are the current hot cars? Thanks to my friends at Yahoo Auto, I was able to determine 8 cars that I wanted to analyze. A few tweaks here and there and I was able to determine basic information which I would need for my analysis.
Great. Since I want to use Excel for the basis of this analysis, I have decided to build a nifty graphical user interface (GUI). OK- great. So what does this mean? For the model intensive folks, this means no more lame-O blue cells (or whatever color you firm uses) to represent inputs or random highlighted cells where you “input”. No, sir. We want to build something that looks prettier and functions more intuitive. That’s why I have decided to add some fancier items such as scrollbar, checkbox, drop down and radio buttons. Something like this…
Before I begin, a few little notes:
- This model contains basic calculations which will not be discussed in this post.
- Conditional formatting strikes again! (Remember, use this technique to quickly troubleshoot + dynamically highlight cells instantly)
- I have officially began my migration to Office 2007! (the file is saved as 2003 XLS format).
As many of you know, Excel 2007 has a pretty fancy item called the Ribbon. We are looking for the Developer tab within the ribbon. Don’t see it there? By default, many of you may not have this option available.
- Office Button > Excel Options > Popular > check “Show Developer tab in the Ribbon”. (Alt F > I > Alt D)
Now that we have our Developer tab sitting nicely on our Ribbon. Let’s discuss why we added this tab.
- Now let’s look for the ActiveX controls. Developer > Insert (Alt L > I). Here are some pretty nifty inputs we can utilize to make the GUI that we so desired.
- Each of these ActiveX controls has their own individual properties including Appearance + Characteristics. There are tons of options, so please take the time to explore each individual option.
- Before you can modify the properites of each ActiveX control, you must first enter “Design Mode”. Developer > Design Mode (Alt I > DM). To view each ActiveX control’s property simply choose an existing item and choose Properties within the Developer tab.
- Of all the options, there is one which is VITAL. It is known as LinkedCell. This is the link between the ActiveX control and Excel. Make sure you set this property to refer to the Excel Spreadsheet.
- There are other useful properties as well, but I will let all of you dig into the spreadsheet to figure them out! I’ll update the post if anything is confusing.
At the very end, we are able to use the GUI + the settings. Voila. Here are our filtered results!
Comments are now closed.