Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Nice!

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Agreed, more than that isn't very useful, and would have a negative impact on performance.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Not a bad idea indeed. I'd limit it to 10 cells or something like that though.

    EDIT: utility is updated so it shows multiple cells (max 10) when you select a range of cells.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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?

    Fred

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Good point about the refresh.
    And I like the row number idea. Not sure if I'll adopt it.

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

    I just open the file itself when I want to use it, so I guess the answer is neither <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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.

    Fred

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Wel, I don't see the issues you describe at all. Makes no difference how I start it, nor on what workbook I try it. Are you sure macros are enabled when you load it manually?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    Well, I don't see it either now. I have my macro security level set on medium. Can't explain it.

    Fred

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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).

    Fred

    Private Sub AppEvents_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim iCount As Integer
    With Me
    .tbxWatch.Text = ""
    For iCount = 1 To Application.Min(10, Target.Rows.Count)
    .tbxWatch.Text = .tbxWatch.Text & _
    "ROW #" & Target.Rows(iCount).Row & "--> " & _ ' THIS IS THE ADDED ROW
    Sh.Cells(Target.Rows(iCount).Row, _
    Sh.Range(Me.tbxColumn & "1").Column) & vbNewLine & "--------------" & vbNewLine
    Next
    End With
    End Sub

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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.

    Fred

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Just a heads-up that I have updated this utility. See first message for the link.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •