Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text in Freeze Pane (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text in Freeze Pane (Excel 2003)

    1) Yes, the panes are numbered as follows:

    <table border=1><td align=right>1</td><td align=right>2</td><td align=right>3</td><td align=right>4</td></table>
    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.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in Freeze Pane (Excel 2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text in Freeze Pane (Excel 2003)

    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

Posting Permissions

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