EXCEL Form Controls

welcome back we're going to look self-formed controls for controls are a means that we can use for specifying the the input for a model you can see a representation of the different kinds of form controls available within Excel here on the spreadsheet notice there's a difference between form controls and active X form controls go on the spreadsheet ActiveX are used in macros now the form controls are available off the ribbon under the Developer tab if you don't have the Developer tab showing up in your workbook if you have Excel 2007 then the way that you would would access that is go to the office button Excel options under those options popular and then just select the show Developer tab in the ribbon if you have Excel 2010 from the file tab options customize ribbon and then you would select Developer so those are two different ways of getting to the the form controls okay well let's create an example and then show how to use the form controls what we'll use is a loan scenario so some loan amount annual interest rate number of months and then just using the Excel payment function let's look at how we can use form controls to to control and vary the inputs into this model from the Developer tab insert and then you can you can select on and then place one of these form controls into your model so for example if I want to select the combo box I click on it go to my model draw the the size of that form control which can be resized and place it in the worksheet then to specify the inputs click on that form control and bring up the format control now you can see the format control over here to the right as it's a screen image so you can refer back to it so for format control I need to give an input range and then a link to which is going to to show the outcome input range is I want to build a vary the number of months so this is going to be a man foot range right here g5 to g8 and then I'm going to put a link after I make a selection that link is going to be here in cell b15 and then let me click on OK now if I click off the form control and now click on that and say select 24 no C gives me the value 136 Bay 2 so with the the combo box and also the list box which we'll get to in just a minute when you make a selection which gives you the the position within that range for the value selected so if the combo box or a list box the way we would make use of that if I come up here for a number of months I could use and say an index function so equals index left parenthesis I like this array and then for the road number click on this the cell link that we had and close parentheses and then you'll see that the number two here is showing 36 the second item in that list and so if I change it to 248 you can see the 48 here and then the corresponding payment so it's a very useful way to build a control and and even make it easier for the input for particular model now the list box is a very similar type of control I've already placed it on the worksheet so let me just select it and then bring up the format control notice you have an input range and a cell links a very common interface with what we saw in the combo box let's do the same thing for an input and a settling so an input and we'll specify these number of months and for the cell link let's put it here in cell be 20 and then okay and then you can see instead of just a drop-down with the selection click off of that where we could select within a list particular one and you can see it shows up with the same cell link to give you the position within that range and so in a similar way we could come up here and instead of for my index for the the road numbers of it being be 15 I could just make that be 20 and if I change in my list box you can see it changes the number of months and then changes the pavement okay so that's two of them let's look at two more scroll and the spins I'm going to go to different worksheet for that and so I've already placed on the in the worksheet a scroll bar and a spin button and they have very similar inputs for their form controls current value a min and a max and an incremental change so again let's control the input into the number of months I've decided to have a current day of 24 men of 24 max of 72 an increment of 1 and you can see a very common interface with those inputs again a cell link but in in this case a cell link would go actually back to the model itself so let's go to the scroll bar and illustrate this if I select on it go up to the format control enter 24 for the current 24 to 72 for the men the max incremental change over 1 and then cell link let's place it here and the model itself and then okay click off the form control and as we select on the scroll bar notice as I selected and click on it the number of months is is changing and as those number of months changes than the payment changes spin button and the the same type of interface and the same linkage off the form control into this so other form controls we haven't talked about but they're all very easy to implement and very useful thanks see you next time

Channel: Jim Grayson

Views: 182,017

Rating: 4.7923079 out of 5

Keywords: EXCEL, form controls

Id: 5MZLq7poTzI

Length: 7min 49sec (469 seconds)

Published: Fri Jun 17 2011