Get a month of TabletWise Pro for free! Click here to redeem

What IF Analysis – Using Form Control Buttons from Developer Tab (Spin Bar, Scroll Bar)

5 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.17
You save:  د.إ146.91
List Price:  A$201.46
You save:  A$57.56
List Price:  ৳11,880.65
You save:  ৳3,394.71
List Price:  CA$190.27
You save:  CA$54.36
CHF 94.09
List Price:  CHF 131.73
You save:  CHF 37.64
List Price:  kr922.14
You save:  kr263.48
List Price:  €123.82
You save:  €35.37
List Price:  £110.81
You save:  £31.66
List Price:  HK$1,085.07
You save:  HK$310.04
List Price:  ₹10,524.04
You save:  ₹3,007.08
List Price:  RM597.26
You save:  RM170.66
List Price:  ₦54,219.52
You save:  ₦15,492.40
List Price:  kr1,320.98
You save:  kr377.45
List Price:  NZ$212.97
You save:  NZ$60.85
List Price:  ₱6,921.33
You save:  ₱1,977.66
List Price:  ₨23,247.81
You save:  ₨6,642.70
List Price:  S$194.74
You save:  S$55.64
List Price:  ฿4,384.27
You save:  ฿1,252.74
List Price:  ₺961.22
You save:  ₺274.65
List Price:  R2,346.24
You save:  R670.40
Already have an account? Log In


A warm welcome to everyone. Today we will be talking about scroll bar, a button which is going to manipulate numbers. And that is going to help you in creating financial models simulation exercises forecasting models. So notice, as I click on one edge of the button, the number on the left hand side changes. And those could have been prices or maybe crude oil prices or quantity sold. And immediately as you change the number using the button, the forecasted numbers, which are based on this initial input will also change.

A similar looking like button is also provided, which is called spin button. So although it is almost the same thing as scroll bar, but it doesn't have this cruel option, it just is up and down. That's all. So we'll see how to create this. But first, you must have the developer tab on your screen. I'll quickly show you how to activate Developer tab, whether 2010 or 2013.

Go to the File menu. Click On options, within which go to Customize ribbon. As you click on Customize ribbon, amongst these two boxes, the right hand side one will have an option called Developer tab, simply need to check it on. As you do so you will find Developer tab on your screen. And that is what he wants. So one time exercise once I've activated this Developer tab, you will find this every time you reboot or boot your system.

Now within developer, I'll be asking you to click on the Insert button. Now notice there are similar looking like buttons under two umbrellas. One is form controls, one is ActiveX controls, I would want you to focus on form controls because unlike active x controls, it doesn't require any VBA coding to configure. The first one we're going to focus on is the fourth button from the first row. It's called spin button. I click on it, I draw an horizontal button.

Now the limitation of this Spin button is you can only draw it vertically because the button placement are such that even if you try to make it horizontal, the buttons arrows will only point top and bottom. So let's see how we configure this button so that it controls a particular cell. I right click, I go to Format control. Now, within format control, it tells me which cell we want to link this button with. I click on one of the cells, okay? And I tell that minimum value is going to be let's say 10.

Maximum or the maximum you cannot go beyond 30,000. But I'll keep it till 5000. Now, I will want that when I click on the button, it should increase by units of five and hence the incremental change. Okay, I click outside the button to activate it, it goes live. I click on the top part of the button. You notice it changes by five.

Now how are they insured? That the maximum value under format control would have been let's assume 50. Okay. Notice it returns back to 50. How much ever I click on the top part of the button is not going to go beyond 50. All the although you have the power to supersede the value in that cell.

So this button if you want to delete it or cut it, you simply have to say right click and cut gone, I would want to create a similar looking like button, and that we will be referring by the name of scroll bar. I asked you to go to developer tab, go to Insert, second row, third button This time, it's called scroll bar. Now that one digit scroll bar is you can draw a vertical scroll bar and you can draw an horizontal scroll bar as well. Now, the controls work almost the same as what we saw with spin button. So right click, format control, I go to minimum value, which I keep it as 50 and maximum as hundred and I will go incremental change to be 260 5254 56. That is how the number is going to move.

And it also has an additional option which says p change I want to speed change, you'll get to see the speed change once I get out of this particular prompt and make the button go live. So just keep in mind that currently in our key speed changes stem cell link, as usual, the same cell which is blinking on my screen, okay, I make the button go live by clicking outside. Now I click on one edge of the button. Notice the numbers are increasing by what two implements of two and it ends at 100. Now, if I click somewhere in the middle of the button in this lumens area, you notice how is the number changing by the increment of 10 correct and that is what teaching is all about. So button can be created to manipulate numbers which can be input numbers for a particular model, feed forecasting model financial model for any kind of simulation exercise.

So this is something that we'll be using in our water analysis.

Sign Up