Keep data in a row visible while I scroll

rainyday

New member
Joined
Mar 13, 2014
Messages
1
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
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.
2014-03-23_153609.jpg
 
Last edited:
Top