Watching the content of another cell (2000, XP, 2003)

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

Re: Watching the content of another cell (2000, XP, 2003)

You could expand it to show multiple values if the selection contains more than one row:

Private Sub AppEvents_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)
Dim i As Long
With Me
.tbxWatch.Text = ""
For i = 1 To Target.Rows.Count
.tbxWatch.Text = .tbxWatch.Text & _
Sh.Cells(Target.Rows(i).Row, _
Sh.Range(Me.tbxColumn & "1").Column) & vbCrLf
Next i
End With
End Sub

This could be useful if you select a few cells, but would become tedious if you select cells in thousands of rows.
This will only handle the first area in a multi-area selection; I don't know if accomodating multiple areas would be a good idea, but it could be done.

Re: Watching the content of another cell (2000, XP, 2003)

Jan Karel,

Very useful utility. Kind of like gives you the ability to freeze a column that is NOT at the left side of the screen.

A few comments:
- even though you expanded it to allow watching more than one row, my experiments show the rows must be contiguous. If I select rows 3 and 5, I don't see all the rows.
- since I can at least select multiple rows, I'd think it would be useful to show the row numbers in the watch window.
- I changed the column to watch while the window was open. The watched cells didn't change. It looks like I have to make a selection in the sheet to "refresh" the watch. Even if I just want to change columns w/o changing watched rows. Could a "refresh" button be added to the form to avoid this?

And now for the stupid question: do I use this as an add-in or place this in xlstart so that I can watch cells in my own sheets?

Re: Watching the content of another cell (2000, XP, 2003)

> Feel free to change the utility at will though, it is all yours <g>

And if I could write VBA anywhere fluently, I would. My print utility would have been done by now too. So you know that's not going to happen. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

> I just open the file itself when I want to use it, so I guess the answer is neither
In my experiment, there seems to be a difference. If I just open your file and start a new workbook for my work, I can't seem to access the Watch Other Cells even though the item is in the Tools menu (I just get a bong). This changes as soon as I save my new workbook (give it a name). But if I put your file in XLSTART, then I can access Watch Other Cells even before I do an initial save on a new workbook. I'm sure there are better minds than mine that can explain this.

Re: Watching the content of another cell (2000, XP, 2003)

Jan Karel,

Sometimes I get lucky with VBA.

Here's a 1-line addition to the code for the form to add the row number. Of course, it could be formatted in any way. Since you're the inventor, you should be the keeper. When I tried to save the modified workbook, I got a msg about not being the person who signed the project and I couldn't save it. I'll leave the Refresh button as an exercise for the reader (definitely not me).

Re: Watching the content of another cell (2000, XP, 2003)

Jan Karel,

After my last email (about the change for ROW # but not being able to save the modified workbook), I opened your original and my change was NOT there. Not a surprise. So I added my 1 line again. When I went to close it, I again got the msg about not being the signer of the project and the Signature being discarded. BUT this time, when I opened the workbook, my change was there. But I think you should still be the keeper.

One other "issue" I discovered. If I have text in a watched cell that spans >1 row, the form may not show all the selected rows. My experiment of setting 10 consecutive watched cells to what the form shows as being on 2 lines (82 characters in the watched cell) only allows for a display of 7-1/2 cells. Scroll bar needed? If so, that's definitely not a job for me.