Dueling Excel - "Watch Box": Podcast #1488

Bill: Hey. Welcome back. It's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Givin from ExcelIsFun.
This is episode number 93, Watch Window.
Mike, I know that most of the time it's a duel, you versus me, but this time we've got to join forces.
We've got to defend Excel’s honour.
Great little series here by Larry, 15 Things You Can't Do In Excel For Budgeting, and I've kind of been watching for a few weeks, but number 4 comes up and he says, hey, you can't have a Watch Box.
He has this little floating Watch Box in his software.
I said, whoa, wait, Larry.
Hang on.
Excel?
We have a Watch Box.
It's not called a Watch Box what's called a Watch Window.
Alright.
So, Mike, I'm going to throw it over to you.
I want to have you show everyone hot to set up a Watch Window.
Mike: Thanks, MrExcel.
So, Watch Window.
Watch Windows are pretty cool.
Now, when we create the Watch Window, it's not going to be as cool as Larry's Watch Window, but we can still do it.
Here's a little budget.
We have some labels at the top of the columns.
If I CONTROL+DOWNARROW, I have some totals.
These are formulas and I like to watch these.
Now, I'd like to name each one of these cells.
So, in the Watch Window, it'll have this name and the formula result, and as we change the inputs, the formula results will change.
Now, I'm going to do something totally freaky, right.
I should just click in the cell, come up to the name box, type the name, but, just for fun, I'm going to move these just for a moment, and, by the way I have freeze panes on.
I'm going to move these with the move cursor up here…oops…onto the top just like that, and now I'm going to highlight these cells and I'm going to use the keyboard shortcut CONTROL+SHIFT+F3 to name cells from selection.
Now, watch this.
I'm going to say BOTTOM ROW.
Now, it will name…that cell right there will be…will have this name right here from the bottom row, and then I'm going to cut and paste into the bottom and the name manager will know.
Click OK.
This is not really part of the trick.
This is just doing weird stuff with Excel.
Now I’m going to CONTROL+X, CONTROL+DOWNARROW, CONTROL+V, and now when I click in this cell, look at that.
When I click there, look at that.
So, now, we have our names.
That little cut and pasting was just, like, seeing what extreme things you could do with Excel.
Alright.
Now, let's do Watch Window.
I'm going to highlight these cells, go up to FORMULAS, FORMULA AUDITING.
I have my window scrunched down here and you can't even see it so I'm going to have to scroll over here.
So, I have those cells highlighted.
Go up to FORMULAS, FORMULA AUDITING, and, there it is, WATCH WINDOW.
Alright.
Now, I'm going to add a watch.
I’m going to scoot this back over here.
Actually, I didn't need to highlight them first because now I have to go up to ADD WATCH, well, now they’re there, and then click ADD, and, sure enough…and I can move these columns here, but there's the VALUE and there's the NAME.
Now, I don't want all these other ones so I'm simply going to point to the…if I can move this over here.
I don't want that either.
I want to point to this edge here, like this, and drag it because I don't want to see the formula, I don't want to see the cell name, I don't want to see the sheet, and I don't want to see the budget.
I don't even…here's the formula over here, alright, and now I'm going to scoot this, the outer edge here, all the way like that, and so now I have my little Watch Window.
No way is it as cool as the one Larry has but there it is, and now the whole idea is anywhere now that if I change these, so if I changed all these budget numbers, so that number, when I hit CONTROL+ENTER, will change, right, and so there it is, and some of the other numbers change [ unintelligible – 04:02 ], right?
So, 1, and then, so, that is our Watch Window.
Alright.
Throw it back to MrExcel.
Bill: Did you see that?
Did you see Mike Girvin just giggle a little bit when he copied those totals off the top because he knew, in the whole CREATING NAMES FROM SELECTION, this would be, like, the first time in the history of the world anyone ever got to use BOTTOM ROW.
BOTTOM ROW.
That was cool.
Alright, now, Mike, thank you for this Watch Window.
Great tutorial on using the Watch Window.
I kind of posted a comment on Larry's blog.
I said Larry, hey, I don't think this one counts as something you can't do in Excel because here it is, and Larry shot me back a screenshot.
He says, wait, are you saying that this, it compares to my beautiful Watch Box?
Okay, and I will admit that his Watch Box did have 2 columns, but here, check out this Watch Box.
This Watch Box was created in about 5 minutes using Excel VBA, and so it's not as easy as using the Watch Window but it's certainly something that can happen in Excel.
So, we'll change some values here and so this is the budget column.
Watch that number.
When I press CONTROL+ENTER, the Watch Box automatically updates, and I've used different colors like Larry did.
I've used different columns like Larry did.
Now, let's take a look at exactly how to create this.
Okay.
So, here's regular old Excel spreadsheet.
I'm going to start here, ALT+F11 to get me over to the VBA editor.
If you don't see the project explorer, VIEW, PROJECT EXPLORER or CONTROL+R. Find the workbook.
Here's the workbook, and then do INSERT, USERFORM.
This is going to become our Watch Box.
This PROPERTIES window down here is going to play a big role.
This is called a CAPTION.
Let's type WACTHBOX.
There.
See?
It changes in the caption.
Alright.
Now, what we're going to do is we're going to add a bunch of labels.
We're going to have labels both for the headers.
So, I'll just add a quick little header up here and the CAPTION for that is 2011.
There is something called foreground color.
I can change the color of that.
Choose the SYSTEM COLOR or PALETTE COLOR there, so I’ll make it blue.
I can change the alignment.
There's something called text align at the bottom, and you would go through and create these static labels, so, labels that aren't really going to change, and so on.
We can change the colors as we need, and, then, where the values are going to go…now, we're going to do one extra thing here.
I'm going to hold down the CONTROL key and drag this down.
Where the values are going to go, I want to rename this to be something useful.
So, right now, it starts out as Label 3.
I'm going to call it maybe, like, LABTARGET, alright, because I'm going to be programmatically putting values in there, and so rather than just Label 1, Label 2, Label 3, Label 4, I rename the ones that I'm actually going to use, and I leave the caption blank on these.
We’ll leave the caption blank on these.
So, you would go through, add these fields.
When you get done, kind of resize the whole thing to make it just fit how large it needs to be, and you see right here it's called USERFORM1.
Let's give this a better name.
Let's call it FRMWB.
Okay, so, now, you're going to go through, create all those Watch Box fields, and the important ones, like this one called LABTARGET target, we're going to right-click the Watch Box, say VIEW CODE, and instead of USERFORM CLICK, I want USERFORM INITIALIZE.
UserForm Initialize.
Now, here, we'd be able to say ME., that's the shortcut for the form name, but I'm going to make it FRMWB.LABTARGET.CAPTION is = to, and we could just say RANGE TARGET.VALUE.
That would work perfectly fine, and the other one, I wanted to make sure that there were commas there.
So, I actually did a little bit more than that in the Watch Box that I showed you earlier.
So, let's take a look at that code.
Here, instead of just using the value, I used APPLICATION.WORKSHEETFUNCTION.TEXT and then said that we wanted the range, and also a number format.
So, a little bit harder to get that number format to show up, but, you know, hey, worth it.
You only have to do this once and then the Watch Box is going to work perfectly.
A couple more things.
A quick little module here that says SHOWWATCH.
It says FORMWATCHBOX or whatever the name of the Watch Box is, .SHOW, and then the important thing, the FALSE, says that we're going to leave that up and let someone work with the spreadsheet.
This is actually creating…the question is whether we're creating something called a modal, modal user form, and by FALSE, it lets us make the changes, and then finally, on the worksheet where we're going to be entering that data, we want to look at the code there and do a WORKSHEET CALCULATE where we copy all of the code from our form initialize.
So, again, it's not as easy as using the Watch Window.
I've assigned this CONTROL+SHIFT+W so I can display it whenever I want, but it is a very nice little Excel Watch Box.
It takes admittedly 5 or 10 minutes to get it set up once, but once you get it set up, it's just there, perfectly living in Excel.
Alright, so there we go.
Mike showed how to do a regular Watch Window and I showed how to do it with VBA.
Certainly possible to do that Watch Box in Excel.
So, as I jokingly said to Larry, I said, hey, Larry, if you were giving us 15 things Excel can't do for budgeting, I think you owe us number 16 because this one is certainly possible.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.