VBA to add and modify a Watch window in excel

**bensox** · 04-25-2013, 05:43 AM

I need to add a macro to an excel workbook that will add a watch window, then reduce the width of all the columns except the Value one.

I know the procedure is quick to do once you know it but I need to add a macro because the workbook will be used by people with little knowledge of excel.

Hope that someone can give me a code or clue (I'm new to VBA).

**JosephP** · 04-25-2013, 05:45 AM

you'd find it a lot easier to create a userform that performs your watch rather than trying to alter the watch window I reckon. I believe there's a youtube video of Bill Jelen showing how to do exactly that

**Norie** · 04-25-2013, 06:11 AM

bensox

I don't think it's actually possible to programmaticlly access/manipulate the Watch window using VBA.

Why do you want to?

About the best you can do is display the window and add watches.

See http://www.excelforum.com/excel-programming-vba-macros/714493-programatically-add-watches-to-the-vbe-watch-window.html

**bensox** · 04-25-2013, 10:02 AM

Originally Posted by Norie

bensox

Why do you want to?

About the best you can do is display the window and add watches.

See http://www.excelforum.com/excel-prog...ch-window.html

Good question! I use it as a live 'Post-It' note so the Job List that is on an excel sheet stays visible whilst I work - nothing technical, just thinking outside the box http://www.excelforum.com/images/smilies/wink.gif

Thank you for the link; that has solved my problem as my 'Watch' needs to link to a constant range. Interestingly, the first few times I activated the macro to show the Watch I had to manually manipulate the columns each time but now, when I close and re-activate it keeps the column widths that I want.

**Norie** · 04-25-2013, 10:05 AM

Why would you want to use the Watch window, a debug tool, for that?

Couldn't you create a userform, as Joseph suggested, to display the Job List?

**bensox** · 04-25-2013, 10:20 AM

Norie

'cos I didn't know how to do userforms or what their potential is... I'm going to look into them this evening (not allowed to access Joseph's suggestion during office hours). I didn't know what the Watch Window was for exactly, just found it and thought "what can I do with this?" my posting this wouldn't help my reputation for knowledge but maybe for honesty!!

**Norie** · 04-25-2013, 10:24 AM

Well, for what you want to do a userform should be straightforward.

For example to show a list of items/jobs you could use a listbox populated from a range on a worksheet, or some other source.

If you want to try a userform and need help post back.

**bensox** · 04-26-2013, 08:58 AM

Thanks, Norie
I have been reading various posts and started creating a userform but I can't get it to show. I posted this question as a new thread to keep things tidy:

HTML Code: