Today, a second answer to Steves question about changing numbers in a worksheet by using a graphical control. Today, we use the ActiveX scrollbar control to solve the problem. Episode 649 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Yesterday we had a question from Steve.
Steve said is there some way that we can basically type numbers into a cell by dragging some sort of a graphical control and yesterday I showed how to do that with a chart.
Unfortunately that doesn't work in excel 2007.
So we're gonna do something today, we're going to add an activex control.
So I'm gonna go to "View" "Toolbars" and choose the "Control Toolbox".
Now there's similar controls in the "Forms Toolbar", but I want to use the "Control toolbox" today. It has a bit more Flexibility, I'm going to choose the "Scrollbar" control.
Now I want to make sure that this fills just a certain row like row 2.
So I'm gonna hold down the Alt key while I drag, I'm gonna hold on the Alt and drag for maybe D2 over to G2.
and it's going to create a scrollbar there.
Now I need to set some functionality, some properties for this scrollbar.
So back here in the toolbar, I'm going to go to the second icon the "Properties" icon and we're gonna set a few different items here.
Now let's think about the numbers.
Maybe in this case the budget has to be between 20 and 500000 or something like that, so we can set a min of 20,000 and then set a max of 50,000.
Few more things when they scroll and we have basically a large scroll and a small scroll, small scroll is what happens if they click the arrow at the end of the scroll bar and you know we might want to make that be 1 or 10 depending on how much granularity we want to give them and a large change.
That's if they basically click between the slider at the end of the scroll bar.
Maybe we make that be a 100 so that way they can very quickly get to a certain spot and now the final thing, the most important part of this, this whole bit is that we want to change the linked cell.
So I'm going to click in here and say this is equal to cell B2 and basically, what you'll see when I click out, I have to turn off the design mode, but we have 31788 there.
I can grab the scroll bar and drag somewhere else and change that number.
I can increment it by 10s, the small change, by using the arrows or click somewhere to the right of the this slider to increment it by 100s and basically what you'll do is once you have this first one drawn in, we're going to go back into design mode, and use the Ctrl key to drag and make new copies of this.
I've already made 10 copies. You'll see here.
I'm just at column C and basically have changed the links L on each of the individual sliders so I can change any number in column B, by just dragging the values here.
Of course you'd have some sort of formulas then to total up the budget and say whether they're under it or over. I mean you can imagine, where at a certain level it would be easier for someone to go through it and drag this slider.
I'm to change these numbers and get a nice graphical view, rather than typing the numbers in themselves.
So there you have it.
Thanks to steve for sending in that question and thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
Yesterday we had a question from Steve.
Steve said is there some way that we can basically type numbers into a cell by dragging some sort of a graphical control and yesterday I showed how to do that with a chart.
Unfortunately that doesn't work in excel 2007.
So we're gonna do something today, we're going to add an activex control.
So I'm gonna go to "View" "Toolbars" and choose the "Control Toolbox".
Now there's similar controls in the "Forms Toolbar", but I want to use the "Control toolbox" today. It has a bit more Flexibility, I'm going to choose the "Scrollbar" control.
Now I want to make sure that this fills just a certain row like row 2.
So I'm gonna hold down the Alt key while I drag, I'm gonna hold on the Alt and drag for maybe D2 over to G2.
and it's going to create a scrollbar there.
Now I need to set some functionality, some properties for this scrollbar.
So back here in the toolbar, I'm going to go to the second icon the "Properties" icon and we're gonna set a few different items here.
Now let's think about the numbers.
Maybe in this case the budget has to be between 20 and 500000 or something like that, so we can set a min of 20,000 and then set a max of 50,000.
Few more things when they scroll and we have basically a large scroll and a small scroll, small scroll is what happens if they click the arrow at the end of the scroll bar and you know we might want to make that be 1 or 10 depending on how much granularity we want to give them and a large change.
That's if they basically click between the slider at the end of the scroll bar.
Maybe we make that be a 100 so that way they can very quickly get to a certain spot and now the final thing, the most important part of this, this whole bit is that we want to change the linked cell.
So I'm going to click in here and say this is equal to cell B2 and basically, what you'll see when I click out, I have to turn off the design mode, but we have 31788 there.
I can grab the scroll bar and drag somewhere else and change that number.
I can increment it by 10s, the small change, by using the arrows or click somewhere to the right of the this slider to increment it by 100s and basically what you'll do is once you have this first one drawn in, we're going to go back into design mode, and use the Ctrl key to drag and make new copies of this.
I've already made 10 copies. You'll see here.
I'm just at column C and basically have changed the links L on each of the individual sliders so I can change any number in column B, by just dragging the values here.
Of course you'd have some sort of formulas then to total up the budget and say whether they're under it or over. I mean you can imagine, where at a certain level it would be easier for someone to go through it and drag this slider.
I'm to change these numbers and get a nice graphical view, rather than typing the numbers in themselves.
So there you have it.
Thanks to steve for sending in that question and thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
