Keep data in a row visible while I scroll

rainyday · Mar 13, 2014

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!

p45cal · Mar 23, 2014

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.

Keep data in a row visible while I scroll

rainyday

New member

p45cal

Super Moderator