Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Keep comment or note in view while scrolling

    I have a workbook with multiple sheets. I want to place a note, comment or something (apx. 3.5" x 1.5" in size) that I can view as I scroll through each sheet. Each sheet will have a different note and the top and left panes will be frozen so I can scroll L & R and Up & Down. I want the note to remain in view. The purpose is convenience and efficiency precluding the necessity of jumping from screen to screen to see the references applicable to each sheet. It just seems to make so much sense to have the required references in view on eaqch sheet. I do have dual monitors and could place them there, but I lack the room on already filled screens.

    I have tried everything, but the comment does not remain stationary. It scrolls with the sheet because it is tied to a cell. I could add a cell then merge the cells vertically and type the references and freeze the column, but I cannot freeze the rows. Maybe I am not freezing correctly. The only thing I have found that may work through my research is the "Data Validation" tool, but I cannot get it to work and frankly very confused on its function.

    I would really appreciate some help on this. Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    You could put the stuff you want onto a Form, and then display the Form as modeless, i.e. it remains visible while you scroll up and down or left-and-right.
    Each sheet could have its own Form.
    To show a form as modeless, use something like this..
    Sub showForm()
    UserForm1.Show vbModeless
    End Sub


    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    research97132 (2012-05-01)

  4. #3
    New Lounger
    Join Date
    Mar 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,
    Really appreciate your response, but I have no clue what you are talking about. Could you please walk me through the process or direct me to a tutorial?
    Thanks

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    I have attached a sample file to show what I was suggesting.

    You probably only need to 'freeze the titles' to get what you want.
    See sheet [Data3] in attached file.

    For 'freeze titles', if you put your cellpointer in say, cell [F7],
    and then in the top-panel toolbars (for Excel2003) select
    Window>Freeze Panes, then every column to the left of the selected cell [F7]
    and every row above the selected cell [F7] will be 'frozen'
    i.e. will remain on screen when you scroll up and down or left and right.

    In the sample file, in addition to the 'freeze titles', I display a UserfOrm on sheets[Data1] and [Data2].
    I have added VBA code to automatically show and hide these 'forms' whenever the data sheet is selected or de-selected.
    It is possible to put text messages on these userforms.
    Perhaps using Userforms may be a little too much for you at this time.
    (You could google 'Excel userforms' for more info on how to use these).

    It all depends on what exactly you are trying to achieve.
    For example, you could also use Excels Window>New Window and then use Windows>Arrange to view multiple sheets at the same time.

    Let us know a little more of what you want and we can assist a little more.

    zeddy
    Attached Files Attached Files

  6. #5
    New Lounger
    Join Date
    Mar 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Keep comment or note in view while scrolling

    Zeddy,
    I want to thank you so much for your efforts in helping me with this problem. I have attached the document that needs the note attached. As you can see, I need to scroll all columns and rows due to the size of the document. There is an area to the right that would be a perfect place to position the note /comment. So, as I scroll through the spreadsheet via rows or columns, the note is stationary and always in view. Why? The note is a mini descendancy chart, which allows me to stay focused and organized, for tracking everyone is very much a challenge and this remedy works for me. I have to see the big picture as I work on each individual.
    The note will look like this, which will be a copy of column A without the spaces and a font size of 8:
    1 Joseph Jones
    m. Mary Loveless
    2 Elizabeth Jones
    2 Mary Ann Jones
    m. Henry Barger
    2 Jones, Son
    2 Esther Jane Jones
    Thanks again. Let me know if you need more information. Research
    Attached Files Attached Files

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    See attached file.

    In this file, in [Sheet2] I used formulas to 'pull' the data from [Sheet1] cells [A1:A8]
    I then formatted these to 8pt.
    I then used View > New Window.
    Then Arrange all, side by side.
    Then selected tab [Sheet2] in the right-hand pane.
    Then grabbed the edge of the second window pane to shrink it to the right.
    Then grabbed the left window pane to expand it towards the right.
    Then turned of the headers in the second window pane.

    This allows you to scroll in the left-hand pane up/down and left/right while the second window pane is showing your required data.

    Hopefully, when you open the attached file it will still show the windows arranged as I have described above.

    zeddy
    Attached Files Attached Files

  8. The Following User Says Thank You to zeddy For This Useful Post:

    research97132 (2012-05-08)

  9. #7
    New Lounger
    Join Date
    Mar 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Zeddy,
    Created a new window and shrinking/expanding worked beautifully. I could not do a normal save, but I figured out through help screens, that I can save the configuration as a workspace and thereby open it as I saved it, with the only exception that the panes do not remain frozen. That is easy to fix each time. So, the end result is success and problem solved, thanks to your efforts and persistence. Thanks again,
    Research

Tags for this Thread

Posting Permissions

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