Back in Episode 695, I used the InputBox function in VBA to ask for a couple of answers. In today's podcast, I will show you how to convert that macro to use a custom user form in Excel. Episode 701 shows you how.
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:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. We're going to go back to episode 695, where I created a little bit of VBA code that could be used to scale these numbers.
We'd multiply by something and then add something.
I'm gonna go look at that VBA code, again and I want to convert this from instead of, using the input boxes to using a real live custom Userform.
So, I'm going to choose Insert Userform and you'll see that we get a generic sized Userform but I can scale this to anything I want.
Right now, while this is selected in the Properties window, I'm going to give it a Caption of...
Scale Utility and I need to add some items to this Userform.
I need to add an OK button, a Cancel button and then two places where people can enter information.
So, I'm going to click on the 'ab', the text box here and draw a place where they can enter the scale by the multiply by, of course, we need a label now.
So, I'll click on the A and draw a label in here.
Now luckily, for us, there are only six items that we're going to have here.
I'm going to TextAlignRight.
Because it's very tedious to go through and build a Userform and of course, it looks much more polished than using the input boxes that we used before.
So, I'll call this label, 'Multiply by:', when I click out, you see that it works.
Now, I want to make sure that the next label and text box are exactly the same size.
So, I Ctrl+Click on both and then Ctrl+Drag to make an identical copy and then this one, then I'll say, and then add...
There we go, so we have to multiply it by something and then add, maybe this text box, I want to always have the default, be multiplied by 1, so in the value, I'll click 1 and then the default 4 and then add, the value should be zero.
All right.
Almost done, need an OK button, Caption of course, is OK.
And a Cancel button, so I'll Ctrl+Drag, make a box, the same size and the Caption there is Cancel.
Okay, at this point, I can resize my Userform down to just what I need.
So, we have scale utility...
great! Okay now, we need to add some code behind the Cancel and OK buttons.
So, I'm going to right click on Cancel and say, View Code. This one is easy. It's simply...
'Unload Me'.
Me is the special name for the current Userform that, we're sitting in, Userform 1.
Now, the OK button.
This is gonna be a little bit more difficult right click, View Code and this is what happens, when 'they clicked OK'.
Now pretty much, I can take the code from my module, the other day.
So, I'll go back here to Module 1 and say, 'For Each Cell In Selection'.
All right Now the 'x' and 'y', these are not going to come from input boxes anymore.
It's going to be, me dot TextBox1 dot value and me dot TextBox2 dot value.
Why TextBox1 and TextBox2? That's the order that I added them in.
I could have gone to the Properties and renamed those with something more useful like Tb multiply or Tb then add but right now, we're just trying to get this going here.
So, we have our two little bits of code sitting behind the Userform.
Last thing we have to do is have a module that will run our Userform.
So, I'm going to come back here and I'm actually going to delete all of that code and say, Userform1 dot Show.
All right now, if you remember from episode 695, we had a Hotkey, which was Ctrl+S for scale.
So, I can choose this number, Ctrl+S...
multiply by 1.25 and then add 3, click OK and you'll see that it makes all of those changes very, very quickly.
So, the point here is not how to scale numbers but the point is how to convert existing code, where we're using an input box.
And I have to admit the input box is not the most elegant way to solve the problem to create a simple Userform with actually, six items, two labels, two text boxes and a couple of buttons that make it appear as if there's a custom dialog box design for this very utility.
Well, I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen. We're going to go back to episode 695, where I created a little bit of VBA code that could be used to scale these numbers.
We'd multiply by something and then add something.
I'm gonna go look at that VBA code, again and I want to convert this from instead of, using the input boxes to using a real live custom Userform.
So, I'm going to choose Insert Userform and you'll see that we get a generic sized Userform but I can scale this to anything I want.
Right now, while this is selected in the Properties window, I'm going to give it a Caption of...
Scale Utility and I need to add some items to this Userform.
I need to add an OK button, a Cancel button and then two places where people can enter information.
So, I'm going to click on the 'ab', the text box here and draw a place where they can enter the scale by the multiply by, of course, we need a label now.
So, I'll click on the A and draw a label in here.
Now luckily, for us, there are only six items that we're going to have here.
I'm going to TextAlignRight.
Because it's very tedious to go through and build a Userform and of course, it looks much more polished than using the input boxes that we used before.
So, I'll call this label, 'Multiply by:', when I click out, you see that it works.
Now, I want to make sure that the next label and text box are exactly the same size.
So, I Ctrl+Click on both and then Ctrl+Drag to make an identical copy and then this one, then I'll say, and then add...
There we go, so we have to multiply it by something and then add, maybe this text box, I want to always have the default, be multiplied by 1, so in the value, I'll click 1 and then the default 4 and then add, the value should be zero.
All right.
Almost done, need an OK button, Caption of course, is OK.
And a Cancel button, so I'll Ctrl+Drag, make a box, the same size and the Caption there is Cancel.
Okay, at this point, I can resize my Userform down to just what I need.
So, we have scale utility...
great! Okay now, we need to add some code behind the Cancel and OK buttons.
So, I'm going to right click on Cancel and say, View Code. This one is easy. It's simply...
'Unload Me'.
Me is the special name for the current Userform that, we're sitting in, Userform 1.
Now, the OK button.
This is gonna be a little bit more difficult right click, View Code and this is what happens, when 'they clicked OK'.
Now pretty much, I can take the code from my module, the other day.
So, I'll go back here to Module 1 and say, 'For Each Cell In Selection'.
All right Now the 'x' and 'y', these are not going to come from input boxes anymore.
It's going to be, me dot TextBox1 dot value and me dot TextBox2 dot value.
Why TextBox1 and TextBox2? That's the order that I added them in.
I could have gone to the Properties and renamed those with something more useful like Tb multiply or Tb then add but right now, we're just trying to get this going here.
So, we have our two little bits of code sitting behind the Userform.
Last thing we have to do is have a module that will run our Userform.
So, I'm going to come back here and I'm actually going to delete all of that code and say, Userform1 dot Show.
All right now, if you remember from episode 695, we had a Hotkey, which was Ctrl+S for scale.
So, I can choose this number, Ctrl+S...
multiply by 1.25 and then add 3, click OK and you'll see that it makes all of those changes very, very quickly.
So, the point here is not how to scale numbers but the point is how to convert existing code, where we're using an input box.
And I have to admit the input box is not the most elegant way to solve the problem to create a simple Userform with actually, six items, two labels, two text boxes and a couple of buttons that make it appear as if there's a custom dialog box design for this very utility.
Well, I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
