Text in Freeze Pane (Excel 2003)

Topic

New Reply

I was working on something a while back and am just now getting back to it. There is another thread that I got the code that has been modified slightly in the enclosed Excel file. I have some questions/issues.

1) For the ActiveWindow.Panes(x), how are the panes numbered. It appears that they are numbers 1,2,4,3 – if you are starting in the upper left and working your way clockwise? I did a bunch of searching and could not find anything on this. After working with my sheet, it looks like this is correct.

2) How do I execute my sub from the Workbook Open event? This is what I have:

Private Sub Workbook_Open()
DisplayLabel()
End Sub

3) The DisplayLabel function will work once, but when it gets called again, an error occurs that says “DisplayLabel” cannot be found.

4) The first Do While contains “Cells(IngRow, 7)”, is the 7 the highest row that it will allow IngRow to get to (i.e., so that it will not search up into the freeze pane)?

I have tried to dig into this routine and am afraid I am floundering at this point.

Thanks for any help,

Andy

Reply | Quote

Replies

1) Yes, the panes are numbered as follows:

1	2
3	4

2/3) The Workbook_Open procedure should be in the ThisWorkbook module, not in a worksheet module, and it should look like this:

Private Sub Workbook_Open()
DisplayLabel
End Sub

i.e. no parentheses after DisplayLabel. The DisplayLabel procedure should be in ThisWorkbook too, or – preferably – in a standard module.

4) In Cells(lngRow, 7), 7 is the column number (corresponding to column G).
If you explain what you want to accomplish this time it would be easier to help you.

Reply | Quote

Thanks Hans, I simply want to have the text value in column A show up in A6 as the user scrolls the text value above the freeze pane. then when the next comes up from the bottom or back down from the top, A6 would change accordingly.

Thanks again,

Andy

Reply | Quote

Try this version. It looks at column A.

Sub DisplayLabel()
Dim lngRow As Long
‘Continue only in this workbook
If ActiveWorkbook Is ThisWorkbook Then
‘Continue only if Freeze Panes is on
If ActiveWindow.FreezePanes = True Then
‘Move up in column A until you encounter text
lngRow = ActiveWindow.Panes(3).ScrollRow
Do While Cells(lngRow, 1) = “” And lngRow > 1
lngRow = lngRow – 1
Loop
‘Set A6
Cells(6, 1) = Cells(lngRow, 1)
End If
End If
‘Call myself in 5 seconds
Application.OnTime Now + TimeSerial(0, 0, 5), “DisplayLabel”
End Sub

Reply | Quote