Did you ever have a need to keep a small bit of information ever present as you move through your Worksheet Data? Today, in Episode #1660, Bill shows us how to accomplish this task in Excel 2010 with the use of a Custom Macro and a Userform. This is a relatively straight forward "First VBA Project" Follow along and try out some VBA!
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
MrExcel podcast, Episode 1660; Floating Box stays Visible while Scrolling.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question, I send I'm wonder, if we can create a Floating Box in Excel 2010 with values in it and then when we scroll up or down in the sheet, the box will go up or down.
I think, we know -- what they mean is we want to keep the box visible, even when we scroll.
So like right there, the box is going away.
We wanna make sure that box is always visible.
So here's the steps it's a little bit complicated but not that complicated.
All right, we're switch over to VBA first, Alt+ F11 in VA, we're gonna Insert, Form, we're gonna Insert a Module.
All right, so there's the Module.
Let's go back to the Userform and do that first.
Double click on the Userform, let's make it be the right size, you can make it be any size you want.
Horrible caption of Userform 1, so while the form is selected, find the caption and let's just say Today's Order Total.
All right now, this is write as a blank form, we have to put something on it.
we have a choice of a label or a text box but a text box would let them type something.
I just want to communicate something, so let's draw a label in there.
All right, that should be more than enough size and when I click on this, I see that it's called Label 1.
I can rename this if I want.
Like LAB total, you don't have to do that but you do have to remember this name.
Okay, now some code to make it all work.
We're right click the form and say view code they start out with Userform, click I want to change that to user form Initialize and what we do is me.labTotal.Caption is equal to the format of something.
Comma in quotes, $, £, comma, £, £, 0.00.
I don't need to show them the sentence.
Okay format is just like text in regular Excel but in VBA we call it format.
All right now, when we're going to put in here?
This is gonna be Worksheets, Sheet 1, dot range, E1, dot value and I'm gonna switch back just a parentheses right there.
Worksheet, Sheet 1,Range E1.
I'm switch back to Excel and make sure that's right.
E1, okay good and that's it.
I'm going to save this line of code.
Ctrl+C, cuz I'm gonna need it again later.
We don't need this tiny little Macro up here.
All right, so we have a Userform Initialize that shows us that total but as those totals change, we need something to happen so you see that we're on Sheet 1 down here, so I'm going to go to the Sheet 1 code pane.
I'm gonna double click on Sheet 1 and from the Left drop-down, I'm gonna choose Worksheet, it's the only choice.
From the right drop-down, I'm gonna say Calculate, every time that this sheet calculates, we want to update the Userform with that value and instead of me dot, we have to say here form.
we just call it Userform 1.
That's not very good, hang on.
Let's come here to Userform 1, I'm going to double click, I thought I'd change the name of this, but I guess that was when I was testing.
So instead a Userform what I'm gonna call it FormTotal at that case we have more than one form later and then here this is Formtotal.
All right, so what do we have?
We have an event handler here, that every time that the worksheet calculates, it updates the name on the form.
We have Module 1, which is we're gonna show the form.
All right, FormTotal dot Show and Model.
Model is going to be false.
Usually a Model form takes control and you can only see, you can't do anything else in the spreadsheet but if we make it be a modeless form then it will just stay put.
All right, so to recap, we have I guess 4 elements, we have a Userform where we added a label, we added some Code to that Userform to initialize it with the value from E1, we added some code to Sheet 1 to update the form and then we added a simple little one-line Macro here, to show the form.
Now back in Excel, we're gonna do Alt+F8 there's our show for.
Let's assign this to what?
Ctrl+Shift+F for form or you choose I don't care.
Click OK, click Cancel.
Now, whenever we wanna see that form, Ctrl+Shift+F, it appears and as we change some numbers, see the total updates...
That's beautiful and I'm gonna put it in one place.
I'm gonna put it right there and as I scroll through the data.
It continues to stay in exactly the place where I put it.
All right, so I think that is what we're looking for a Modeless user form that eventually becomes a box.
There's not even a button on here, if we want to get rid of it just click the X and it will go away, Ctrl+Shift+S Our Ctrl+Shift+F for form and it comes back, choose the place to put it.
Hey, thank you for stopping by. We'll see you next time for another netcast MrExcel.
MrExcel podcast, Episode 1660; Floating Box stays Visible while Scrolling.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question, I send I'm wonder, if we can create a Floating Box in Excel 2010 with values in it and then when we scroll up or down in the sheet, the box will go up or down.
I think, we know -- what they mean is we want to keep the box visible, even when we scroll.
So like right there, the box is going away.
We wanna make sure that box is always visible.
So here's the steps it's a little bit complicated but not that complicated.
All right, we're switch over to VBA first, Alt+ F11 in VA, we're gonna Insert, Form, we're gonna Insert a Module.
All right, so there's the Module.
Let's go back to the Userform and do that first.
Double click on the Userform, let's make it be the right size, you can make it be any size you want.
Horrible caption of Userform 1, so while the form is selected, find the caption and let's just say Today's Order Total.
All right now, this is write as a blank form, we have to put something on it.
we have a choice of a label or a text box but a text box would let them type something.
I just want to communicate something, so let's draw a label in there.
All right, that should be more than enough size and when I click on this, I see that it's called Label 1.
I can rename this if I want.
Like LAB total, you don't have to do that but you do have to remember this name.
Okay, now some code to make it all work.
We're right click the form and say view code they start out with Userform, click I want to change that to user form Initialize and what we do is me.labTotal.Caption is equal to the format of something.
Comma in quotes, $, £, comma, £, £, 0.00.
I don't need to show them the sentence.
Okay format is just like text in regular Excel but in VBA we call it format.
All right now, when we're going to put in here?
This is gonna be Worksheets, Sheet 1, dot range, E1, dot value and I'm gonna switch back just a parentheses right there.
Worksheet, Sheet 1,Range E1.
I'm switch back to Excel and make sure that's right.
E1, okay good and that's it.
I'm going to save this line of code.
Ctrl+C, cuz I'm gonna need it again later.
We don't need this tiny little Macro up here.
All right, so we have a Userform Initialize that shows us that total but as those totals change, we need something to happen so you see that we're on Sheet 1 down here, so I'm going to go to the Sheet 1 code pane.
I'm gonna double click on Sheet 1 and from the Left drop-down, I'm gonna choose Worksheet, it's the only choice.
From the right drop-down, I'm gonna say Calculate, every time that this sheet calculates, we want to update the Userform with that value and instead of me dot, we have to say here form.
we just call it Userform 1.
That's not very good, hang on.
Let's come here to Userform 1, I'm going to double click, I thought I'd change the name of this, but I guess that was when I was testing.
So instead a Userform what I'm gonna call it FormTotal at that case we have more than one form later and then here this is Formtotal.
All right, so what do we have?
We have an event handler here, that every time that the worksheet calculates, it updates the name on the form.
We have Module 1, which is we're gonna show the form.
All right, FormTotal dot Show and Model.
Model is going to be false.
Usually a Model form takes control and you can only see, you can't do anything else in the spreadsheet but if we make it be a modeless form then it will just stay put.
All right, so to recap, we have I guess 4 elements, we have a Userform where we added a label, we added some Code to that Userform to initialize it with the value from E1, we added some code to Sheet 1 to update the form and then we added a simple little one-line Macro here, to show the form.
Now back in Excel, we're gonna do Alt+F8 there's our show for.
Let's assign this to what?
Ctrl+Shift+F for form or you choose I don't care.
Click OK, click Cancel.
Now, whenever we wanna see that form, Ctrl+Shift+F, it appears and as we change some numbers, see the total updates...
That's beautiful and I'm gonna put it in one place.
I'm gonna put it right there and as I scroll through the data.
It continues to stay in exactly the place where I put it.
All right, so I think that is what we're looking for a Modeless user form that eventually becomes a box.
There's not even a button on here, if we want to get rid of it just click the X and it will go away, Ctrl+Shift+S Our Ctrl+Shift+F for form and it comes back, choose the place to put it.
Hey, thank you for stopping by. We'll see you next time for another netcast MrExcel.
