Sometimes I need to analyse data (row-by-row) in a worksheet with a lot of columns. Sometimes I then want to see information from a cell in a column way off to the far right together with a couple of columns on the left side of the sheet. Of course you can split the window, freeze the panes, but this isn’t always sufficient.
I devised a tiny utility that shows a modeless window (only works in XL2000 and up) which displays the content of a cell in a set column on the same row:
The form can be resized to get it out of the way and accomodate for the amount of information you want to see.
Find it here.
You can also bring up the watch window, at least in XL2003 you can.
Click on Tools/Customize and find the toolbar ‘Watch Window’. It give some more info than just the value. It shows the Book Name, Sheet Name, User Defined Name, Cell, Value, Formula.
You can also have multiple watches going. It’s a simplified version of the VBA Watch window.
No, the watch window doesn’t help here, since it watches at a fixed cell.
This utility watches dynamically: it keeps the same row as the selected cell.
I haven’t tried it, but this appears like it would be a really cool tool.
I got it, sorry about that.
That is a useful tool.
Very nice Jan –
What happens if you remove the form caption can it still be resized, i wonder what it might be like if it was transparent – would you mind if i tried these things?
Ross: Sure, go ahead, have fun with it.
Nice, I don’t know very much about VBA but by some of the comments in the code it looks to me that if you resize the form then the next time the form is opened it should open to that size, is that right? when I open the form back up it opens back up full size
Yes, it can do that but I think I switched that off.
How would I switch it back on?
Thanks Tim
Very nice! Useful for my 150 columns wide actuarial projections.
I played around with project, I rather over looked that fact that making it captionless means that you cant move the form!!! The transparent form looks ok. Let me know if anyone wants to take a look
Ross,
I know of two solutions for this, neither one of them is beautiful. But it might just be enough for personal use.
One of them is obvious: just drag the form around by resizing it using two oposite corners. (Not my favorite). But if you reenable the storage of the position of the form it might suit you anyway.
Another way is to define a click-on-form-event and make it display the caption for a period of 5 seconds. That would be enough for you to grab the form and drag it around. After you release it the caption would disappear again.
Gerrit
I am using Excel 2000 and I simply Copy one or more cells than Shift-Edit Paste Picture Link to where I need it. I can format and move that picture as required.
Robin
Hi Robin:
Nice idea, but I expect that doesn’t change when you move to a different row?
Ross:
Sure, send it over!
BTW: can’t you also make the titlebar appear small somehow?
Hi,
I’m off out on the town now, i will tidy the thing up and send it to you Jan. I used Colos code for both the trans and the caption, but Steven’s/Tim Clems form fun lets you have a small captions.
Gerrit,
My idea was to use use some code like i did with my popup form (http://www.methodsinexcel.co.uk/downloads.html), and the forms mouse down event, i think it should work ok – I’ll take a look on saturday afternoon ;-)))
[…] Yesterday I was playing around with Jan Karel Pieterse Watch Other Cell tool. I wanted to make the user form captionless and transparent. That’s easy enough to do but a problem arose. With the caption removed the form could not be moved. One work around was to use the forms mouse move event and an API Call to hook the cursor location. Another API call is made to reposition the form. The Down and Up events are used to “turn on” weather or not we what to move the form. For the user it is much the same as a normal move process. […]
Does anyone know how to make the Excel Watch Window appear on top of other applications. Or in deed how to make a ticker (either rolling or static)that updates from excel and is constantly shown on the screen.
Help Please
is there any way to trap the mouse move on image inside shape ?
only mouse moves as availble for charts?