Results 1 to 2 of 2

Thread: Keep data in a row visible while I scroll

  1. #1

    Keep data in a row visible while I scroll



    Register for a FREE account, and/
    or Log in to avoid these ads!

    No I don't want a to freeze panes

    I have frozen panes already - I keep a table with accommodations - arrivals and departures - the columns are days (several months worth this is frozen) and the rows the room number (this is also frozen), the rows after the room number list the person in the room at a particular time (column date). Some people stay in the same room all the time - but since their name is entered at the beginning of the table when I scroll to the end of the row I don't know who is in the room... is there a way to have that data that I have entered at the beginning of the row be visible as I scroll the row to the end? Some sort of floating message, or the name shifted forward and back as I scroll through the table?

    Thanks!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    try this in the sheet's own code module:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldTarget As Range
    If Not OldTarget Is Nothing Then OldTarget.Validation.Delete
    If Target.Cells.Count = 1 Then
      If IsEmpty(Target) Then
        With Target.Validation
          .Delete
          .Add Type:=xlValidateInputOnly
          .InputMessage = Target.End(xlToLeft).Value
          Set OldTarget = Target
        End With
      End If
    End If
    End Sub
    It uses a data validation message which appears when the cell is selected and contains the value of the next non-empty cell to the left. It deletes that data validation when another selection is made. If this is satisfactory, it could be tweaked to conserve any data validation if you already have it for other purposes.
    Click image for larger version. 

Name:	2014-03-23_153609.jpg 
Views:	5 
Size:	6.1 KB 
ID:	2160
    Last edited by p45cal; 2014-03-23 at 04:42 PM.

Posting Permissions

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