Dueling: Percentage Scrollbar - 1045 - Learn Excel Podcast Video

Get it off your chest – be the first to comment on this video!

Dueling: Percentage Scrollbar - 1045 - Learn Excel Podcast

Bill: Hey, I’m Bill Jelen, Mr. Excel.com, I got a cool Excel tip for you today. Mike: Hey, this is Mike Gel Girvin, in Excel is Fun in Youtube, and I have different way to do that. Bill: Alright, well, welcome back, it's Friday, that means it’s another Dueling Excel podcast, I’m Bill Jelen from Mr. Excel, we’re joined by Mike Gel Girvin from Excel is Fun. Question today from Youtube, I wanna say it’s EZAK007, and ask about something about scroll bar for percentages. So right here, this 5 percent, we’d like to have a scroll bar, a nice little slider, so 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, active X control are back here in the developer tab. See we have insert, there’s form controls. Mike’s gonna talk about that, I’m gonna talk about the active X control forms control, easier to set up, active X control, a little bit more power. So right here in the scroll bar, I wanna draw just a nice big scroll bar in, you can make this whatever size you’d like, now, you notice that we are automatically in design mode that allows us to adjust the scroll bar and I wanna go into properties. Couple of things I wanna change here, I’m interested in the min, the max, small change, large change. So the min is zero, that’s perfect, max, I wanna have the max go up to 18.9 percent and I wanna enter that as a 189, because these values have to be integers. You can put decimals in there, so a min of zero max of a hundred and 89 and I’m gonna make the large change be in increments of 10, so every time we do a large change, it’s gonna go up by one percent, and then the small change it’s great there as an increment of one. Finally, I’m going to put the result of this, the link cell right here, link cell D1, really I put it out in D1, but let’s just put it here in D1, so we’ll 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 ten, that’s the large change. When I click on the actual arrow goes up by one, that’s the small change of course, people can just use the slider here to make it all work. Now the last piece of this is that we can say that we wanna use cell equal D1 divided by 1000, so there’s 6.2 percent, 6.3 percent, 6.4 percent, or I could just grab the slider and very easily get that number. It’s great for people that, our keyboard people, or mouse people, they can as very simply come in here. Now the real power, I think, is, in this active X controls, we’re gonna actually right some VBA behind the scenes. So, I’m going to come in and say view code, and initially they have something here called scroll bar one change, what I could say, let’s see where’s that percentage, that percentages in cell B4. I could say range B4 dot value equal to scroll bar one dot value divided by 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 cell all together. It’s also interesting, you’ll notice here as I scroll, that VBA is not firing, so you’ll 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 here. Scroll bar one, we can also say that I wanna see the scroll action and here we can say range B4 is equal to the scroll and that’s a slight improvement because now as I move that it’s automatically gonna be updated in the individual cell, great way to go. So that’s an active X control, you can write some VBA, you can see there in the beginning by being a little bit clever and writing, you know, 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 it’s the forms control, we’re gonna send it over to Mike, and Mike will show you how to do this with a forms control. Mike: Thanks Mr. Excel, hey, you got to love that VBA, you do your active X control, write a little VBA and then you don’t need that helper cell. Now for a form, I’m gonna have to use a helper cell, but I don’t have to write any VBA code. Okay, I’m gonna copy this, point to the edge here and then right click and drag down here, and in the popup menu, I’ll say copy here. Now we go to the same place and by the way this scroll with VBA, you gotta have that dot xlsm, that m on the end, you don’t wanna use the dot xlsx, because that doesn’t allow VBA. Hey, but if you have a dot xlsx or you don’t wanna do VBA, then we’ll do the forms. We come down here, there's the scroll button click scroll bar, click and drag, and then I’m gonna right click and go to format control. And sure enough there is current value, minimum, maximum, I’ll do exactly the same, 189, incremental change one, page change, that’s large change, it’s ten, and I’ll link it to say this cell right here, click OK. Now, as I click off to the side and now as I click here, you can see, it’s incrementing a large change by ten, if I click here, it’s jumping up by one. Then I come here, equals that cell right there, divided by 1000. So now you can see, boom, boom, boom, it’s working just fine, ten percent and then here’s our small change. So, definitely active X control VBA, great, coz you don’t need that helper cell, but be sure to do the dot xlsm, otherwise the forms scroll bar right there pretty straight forward for those of us who are not that good at VBA. Alright, we’ll see you next trick. Bill: Hey, Mike, I think the forms control is the way to go, you know, I just reopened this file towards the closing here, get the security warning, that’s in active X content has been disabled. So by using the forms control it doesn’t get the workbook mark as a macro workbook and you don’t have to worry about all theses hassles. So a great way to go, I wanna thank everyone for stopping by, behalf of Mike and myself, we’ll see you next time for another Dueling Excel podcast.

No Links were listed yet. Go ahead and share!

Bill: Hey, I’m Bill Jelen, Mr. Excel.com, I got a cool Excel tip for you today.
Mike: Hey, this is Mike Gel Girvin, in Excel is Fun in Youtube, and I have different way to do that.
Bill: Alright, well, welcome back, it's Friday, that means it’s another Dueling Excel podcast, I’m Bill Jelen from Mr. Excel, we’re joined by Mike Gel Girvin from Excel is Fun. Question today from Youtube, I wanna say it’s EZAK007, and ask about something about scroll bar for percentages. So right here, this 5 percent, we’d like to have a scroll bar, a nice little slider, so 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, active X control are back here in the developer tab. See we have insert, there’s form controls. Mike’s gonna talk about that, I’m gonna talk about the active X control forms control, easier to set up, active X control, a little bit more power. So right here in the scroll bar, I wanna draw just a nice big scroll bar in, you can make this whatever size you’d like, now, you notice that we are automatically in design mode that allows us to adjust the scroll bar and I wanna go into properties. Couple of things I wanna change here, I’m interested in the min, the max, small change, large change. So the min is zero, that’s perfect, max, I wanna have the max go up to 18.9 percent and I wanna enter that as a 189, because these values have to be integers. You can put decimals in there, so a min of zero max of a hundred and 89 and I’m gonna make the large change be in increments of 10, so every time we do a large change, it’s gonna go up by one percent, and then the small change it’s great there as an increment of one. Finally, I’m going to put the result of this, the link cell right here, link cell D1, really I put it out in D1, but let’s just put it here in D1, so we’ll 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 ten, that’s the large change. When I click on the actual arrow goes up by one, that’s the small change of course, people can just use the slider here to make it all work. Now the last piece of this is that we can say that we wanna use cell equal D1 divided by 1000, so there’s 6.2 percent, 6.3 percent, 6.4 percent, or I could just grab the slider and very easily get that number. It’s great for people that, our keyboard people, or mouse people, they can as very simply come in here. Now the real power, I think, is, in this active X controls, we’re gonna actually right some VBA behind the scenes. So, I’m going to come in and say view code, and initially they have something here called scroll bar one change, what I could say, let’s see where’s that percentage, that percentages in cell B4. I could say range B4 dot value equal to scroll bar one dot value divided by 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 cell all together. It’s also interesting, you’ll notice here as I scroll, that VBA is not firing, so you’ll 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 here. Scroll bar one, we can also say that I wanna see the scroll action and here we can say range B4 is equal to the scroll and that’s a slight improvement because now as I move that it’s automatically gonna be updated in the individual cell, great way to go. So that’s an active X control, you can write some VBA, you can see there in the beginning by being a little bit clever and writing, you know, 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 it’s the forms control, we’re gonna send it over to Mike, and Mike will show you how to do this with a forms control.
Mike: Thanks Mr. Excel, hey, you got to love that VBA, you do your active X control, write a little VBA and then you don’t need that helper cell. Now for a form, I’m gonna have to use a helper cell, but I don’t have to write any VBA code. Okay, I’m gonna copy this, point to the edge here and then right click and drag down here, and in the popup menu, I’ll say copy here. Now we go to the same place and by the way this scroll with VBA, you gotta have that dot xlsm, that m on the end, you don’t wanna use the dot xlsx, because that doesn’t allow VBA. Hey, but if you have a dot xlsx or you don’t wanna do VBA, then we’ll do the forms. We come down here, there's the scroll button click scroll bar, click and drag, and then I’m gonna right click and go to format control. And sure enough there is current value, minimum, maximum, I’ll do exactly the same, 189, incremental change one, page change, that’s large change, it’s ten, and I’ll link it to say this cell right here, click OK. Now, as I click off to the side and now as I click here, you can see, it’s incrementing a large change by ten, if I click here, it’s jumping up by one. Then I come here, equals that cell right there, divided by 1000. So now you can see, boom, boom, boom, it’s working just fine, ten percent and then here’s our small change. So, definitely active X control VBA, great, coz you don’t need that helper cell, but be sure to do the dot xlsm, otherwise the forms scroll bar right there pretty straight forward for those of us who are not that good at VBA. Alright, we’ll see you next trick.
Bill: Hey, Mike, I think the forms control is the way to go, you know, I just reopened this file towards the closing here, get the security warning, that’s in active X content has been disabled. So by using the forms control it doesn’t get the workbook mark as a macro workbook and you don’t have to worry about all theses hassles. So a great way to go, I wanna thank everyone for stopping by, behalf of Mike and myself, we’ll see you next time for another Dueling Excel podcast.

Transcription by: Scribe4you Transcription Services