Izhak asks how to add a scrollbar to a worksheet for selecting a percentage. In this dueling Excel podcast Episode 1045, Bill and Mike will show you two ways to solve the problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
MrExcel: Hey, I'm Bill Jelen, from "MrExcel.com".
I've got a cool Excel tip for you, today.
Mike: Hey, this is my choker, an excellent spot on YouTube.
I have a different way to do that.
MrExcel: Alright! Well, welcome back. It's Friday that means is another dueling excel podcast.
I'm Bill Jelen, from MrExcel.
Will be joined by Mike Girvin, from Excel Is Fun.
Question today from YouTube.
It say... It's Issac_007 asked about setting up a scroll bar for percentages.
So, right here this [ 5% ], we like to have a scrollbar, a nice little slider.
That way someone can change that input cell and see a different calculation .
Now, here's what we have to do in Excel 2007.
Go to the office button, down here at the bottom, choose excel options and then show developer tab in the ribbon. All of the form controls and activeX controls are back here on the developer tab.
You see, we have [ insert ] there's form controls.
Mike's going to talk about that.
I'm going to talk about the activeX control, forms control, easier to set up.
ActiveX control, a little bit more power.
So, right here I have the scroll bar.
I'm going to draw just a nice big scroll bar, in you can make this whatever size you'd like.
And now you notice that we are automatically in design mode that allows us to adjust the scroll bar, and I want to go into properties.
A couple of settings, we're going to change here.
I'm interested in the min, the max, small change, large change.
So, the min is 0. That's perfect. Max...
I'm going to have the max ago up to 18.9%.
And I'm going to enter that as 189 because these values have to be integers.
You can't put decimals in there.
So, a min of zero, max of 189, and I'm going to make the large change be in increments of 10.
So, every time we do a large change its going to go by 1% and then the small change.
is great there as an increment of 1.
Finally, I'm going to put the result of this.
The link cell right here, in cell D1. Really I'd put it out in Z1, but let's just put it here in D1.
So, we can see where it is D1 and we are good to go.
So, I'll close the properties window, exit design mode and you see how this works.
When I click here between the slider and the end, it goes up by 10, that's the large change.
When I click on the actual arrow goes up by one, that's the small change.
And of course people can just use the slider here, to make it all works.
Now, that last piece of this is that we can say that we want to use cell equal D1/1000.
So, there's 6.2%.
6.3%, 6.4%, or I can just grab a slider and very easily get that number.
It's great for people that aren't keyboard people, they're mouse people.
They can very simply come in here.
Now the real power I think is, in these activeX controls.
We can actually, write some VBA behind the scenes.
So I'm going to come in and say view code and initially they have something here called scroll bar1_change.
What I could say is, let's see where that percentages.
That percentages in, cell B4.
I could say range("B4").value=scrollbar1.
.value/1000.
And then I wouldn't have to use a link cell at all.
It's going to be exit design mode.
It's going to be directly written in this cell.
We can get rid of that link sell, all together.
It's also interesting you'll notice here that as I scroll, that VBA is not firing.
So, you see in cell D1 the link cell is updating, but because I'm using VBA to write, B4 there it doesn't change.
Until, I let go.
So, let's take a look at some other values.
We have your scrollbar_1, we could also say that I want to see the scroll action and here we can say range B4=scroll.
And that's a slight improvement because now as I move that it's automatically going to be updated in the individual cell.
Great way to go.
So, that's an activex control.
You can write some VBa, you can see there at the beginning by being a little bit clever and writing.
You know, a number that's a thousand times larger than what I want it to be and using a formula here.
It was all working out a much easier way to go.
Though, I think is the forms control.
We're going to send it over to Mike and Mike will show you how to do this with a forms control.
Mike: Thanks! MrExcel.
Hey!
You gotta love that VBA, you do your activeX control.
Write a little VBA and then you don't need that helper cell.
Now, for form. I'm going to have to use a helper cell.
But, I won't have to write any VBa code.
Here, I'm going to copy this point to the edge here and then right click, and drag down here and then the pop-up menu will say copy here now.
We go to the same place and by the way, this scroll with VBA you got to have that '.xlsm'.
That 'm' on the end, you don't want to use the '.xlsx'.
Because that doesn't allow VBA.
Hey! But, if you have a .xlsx, or you don't want to do VBA then we'll do the forms.
We come down here, there's the scroll button, click [ scroll bar ] click and drag.
Then, I'm going to right click and go to Format control.
Sure enough, there is current value, minimum, maximum. I'll do exactly the same 189, incremental change 1, Page change, that's large change is 10, and I'll link it to say this cell right here.
Click [ OK ].
Now, as a click of to the side and now as I click here.
You can see, it's incrementing a large change by 10 If I click here, it's jumping up by one.
Then I come here equals that cell right there 1000.
So, now you can see, boom! Boom! Boom!
It's working just fine 10%, and then here's our small change.
So, definitely activeX control VBA, a great because you don't need that helper cell.
But, be sure and do the .xlsm.
Otherwise, the form scroll bar right there, pretty straight forward for those of us who are not good at VBA.
We'll see you next trick.
MrExcel: Hey! Mike. I think the forms control is the way to go, you know.
I just re-open this file to record the closing here.
Get the security warning that some activeX Content has been disabled.
So, by using the forms control it doesn't get the the workbook mark is a macro workbook, and you don't have to worry about all of these hassles.
So, great way to go.
Want to thank everyone for stopping by.
Behalf of Mike and myself.
We'll see you next time for another dueling Excel podcast.
I've got a cool Excel tip for you, today.
Mike: Hey, this is my choker, an excellent spot on YouTube.
I have a different way to do that.
MrExcel: Alright! Well, welcome back. It's Friday that means is another dueling excel podcast.
I'm Bill Jelen, from MrExcel.
Will be joined by Mike Girvin, from Excel Is Fun.
Question today from YouTube.
It say... It's Issac_007 asked about setting up a scroll bar for percentages.
So, right here this [ 5% ], we like to have a scrollbar, a nice little slider.
That way someone can change that input cell and see a different calculation .
Now, here's what we have to do in Excel 2007.
Go to the office button, down here at the bottom, choose excel options and then show developer tab in the ribbon. All of the form controls and activeX controls are back here on the developer tab.
You see, we have [ insert ] there's form controls.
Mike's going to talk about that.
I'm going to talk about the activeX control, forms control, easier to set up.
ActiveX control, a little bit more power.
So, right here I have the scroll bar.
I'm going to draw just a nice big scroll bar, in you can make this whatever size you'd like.
And now you notice that we are automatically in design mode that allows us to adjust the scroll bar, and I want to go into properties.
A couple of settings, we're going to change here.
I'm interested in the min, the max, small change, large change.
So, the min is 0. That's perfect. Max...
I'm going to have the max ago up to 18.9%.
And I'm going to enter that as 189 because these values have to be integers.
You can't put decimals in there.
So, a min of zero, max of 189, and I'm going to make the large change be in increments of 10.
So, every time we do a large change its going to go by 1% and then the small change.
is great there as an increment of 1.
Finally, I'm going to put the result of this.
The link cell right here, in cell D1. Really I'd put it out in Z1, but let's just put it here in D1.
So, we can see where it is D1 and we are good to go.
So, I'll close the properties window, exit design mode and you see how this works.
When I click here between the slider and the end, it goes up by 10, that's the large change.
When I click on the actual arrow goes up by one, that's the small change.
And of course people can just use the slider here, to make it all works.
Now, that last piece of this is that we can say that we want to use cell equal D1/1000.
So, there's 6.2%.
6.3%, 6.4%, or I can just grab a slider and very easily get that number.
It's great for people that aren't keyboard people, they're mouse people.
They can very simply come in here.
Now the real power I think is, in these activeX controls.
We can actually, write some VBA behind the scenes.
So I'm going to come in and say view code and initially they have something here called scroll bar1_change.
What I could say is, let's see where that percentages.
That percentages in, cell B4.
I could say range("B4").value=scrollbar1.
.value/1000.
And then I wouldn't have to use a link cell at all.
It's going to be exit design mode.
It's going to be directly written in this cell.
We can get rid of that link sell, all together.
It's also interesting you'll notice here that as I scroll, that VBA is not firing.
So, you see in cell D1 the link cell is updating, but because I'm using VBA to write, B4 there it doesn't change.
Until, I let go.
So, let's take a look at some other values.
We have your scrollbar_1, we could also say that I want to see the scroll action and here we can say range B4=scroll.
And that's a slight improvement because now as I move that it's automatically going to be updated in the individual cell.
Great way to go.
So, that's an activex control.
You can write some VBa, you can see there at the beginning by being a little bit clever and writing.
You know, a number that's a thousand times larger than what I want it to be and using a formula here.
It was all working out a much easier way to go.
Though, I think is the forms control.
We're going to send it over to Mike and Mike will show you how to do this with a forms control.
Mike: Thanks! MrExcel.
Hey!
You gotta love that VBA, you do your activeX control.
Write a little VBA and then you don't need that helper cell.
Now, for form. I'm going to have to use a helper cell.
But, I won't have to write any VBa code.
Here, I'm going to copy this point to the edge here and then right click, and drag down here and then the pop-up menu will say copy here now.
We go to the same place and by the way, this scroll with VBA you got to have that '.xlsm'.
That 'm' on the end, you don't want to use the '.xlsx'.
Because that doesn't allow VBA.
Hey! But, if you have a .xlsx, or you don't want to do VBA then we'll do the forms.
We come down here, there's the scroll button, click [ scroll bar ] click and drag.
Then, I'm going to right click and go to Format control.
Sure enough, there is current value, minimum, maximum. I'll do exactly the same 189, incremental change 1, Page change, that's large change is 10, and I'll link it to say this cell right here.
Click [ OK ].
Now, as a click of to the side and now as I click here.
You can see, it's incrementing a large change by 10 If I click here, it's jumping up by one.
Then I come here equals that cell right there 1000.
So, now you can see, boom! Boom! Boom!
It's working just fine 10%, and then here's our small change.
So, definitely activeX control VBA, a great because you don't need that helper cell.
But, be sure and do the .xlsm.
Otherwise, the form scroll bar right there, pretty straight forward for those of us who are not good at VBA.
We'll see you next trick.
MrExcel: Hey! Mike. I think the forms control is the way to go, you know.
I just re-open this file to record the closing here.
Get the security warning that some activeX Content has been disabled.
So, by using the forms control it doesn't get the the workbook mark is a macro workbook, and you don't have to worry about all of these hassles.
So, great way to go.
Want to thank everyone for stopping by.
Behalf of Mike and myself.
We'll see you next time for another dueling Excel podcast.
