Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display Table Title in Freeze Panes (XP / 2003)

    I've got another difficult task requiring the expertise of the Excel wizards of the Lounge. Each month the Actuarial department runs a report that automatically updates (expands) an Excel worksheet containing 4 tables (2 across & 2 down). I've attached a cut-down version of a sample workbook -- in a full version, each of the four tables in every sheet would show ten years worth of monthly data (120 columns across and 120 rows down), and there would be sheets for each month of the current year. The spreadsheet is then used by the finance department to make various decisions each month.
    Because of the size of the tables, it's difficult to be certain of which table you are viewing in the "window" as you scroll down or right. This recently cause an issue when someone used a figure from table "D-Service W/H" when they thought they were in table "A-Facility Paid". As a result I was tasked with immediately finding a way to keep the "title" of the current table visible at all times.
    My first suggestion -- move each table into a separate worksheet -- met with cold stares from both the CFO and the Controller, so that idea didn't fly. My second idea was to add a column and a row in the Freeze Panes which repeated the current table ID every few cells across the top and down the left side. Unfortunately, that one was vetoed by those who can't abide "clutter".
    My latest idea (which has already met rave reviews by all) is to find a means to display the ID in cell A2 (within the Freeze Panes). Since the size of the tables is always expanding, the only title that remains in the same cell is the one in B3. Can anyone think of a way to identify when the view has scrolled past another column or row with a table title so that I can display the title in A2?
    Thanks in advance for your thoughts and effort,

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

    Re: Display Table Title in Freeze Panes (XP / 2003)

    As far as I know, there is no On Scroll event or something like that, so you'd need to check periodically. Perhaps you can adapt the following macro to your needs. Once started, it'll run every 5 seconds (you can modify the interval, but don't make it too short, it'll interfere with performance)

    Sub DisplayLabel()
    Dim lngRow As Long
    Dim lngCol 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 B until you encounter text
    lngRow = ActiveWindow.Panes(4).ScrollRow
    Do While IsNumeric(Cells(lngRow, 2)) And lngRow > 1
    lngRow = lngRow - 1
    Loop
    ' Move left in row 3 until you encounter text
    lngCol = ActiveWindow.Panes(4).ScrollColumn
    Do While IsNumeric(Cells(3, lngCol)) And lngCol > 1
    lngCol = lngCol - 1
    Loop
    ' Set A2
    Cells(2, 1) = Cells(lngRow, lngCol)
    End If
    End If
    ' Call myself in 5 seconds
    Application.OnTime Now + TimeSerial(0, 0, 5), "DisplayLabel"
    End Sub

    You must either start this macro once manually, or call it from the Workbook_Open event.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Display Table Title in Freeze Panes (XP / 2003)

    Perhaps the best solution (since the powers that be don't like the obvious solution) would be restrict viewing to one table at a time.

    I have adapted your workbook (copy attached) using the Worksheet_SelectionChange event to hide all but the table of immediate interest. Simply select the table name to view. The name of this table will be on display in A1.

    I did mess about with your columns, for convenience. As the workbook stands, Columns B & Q should contain only the 4 Table Names (cells B3,B31,Q3,Q31). I am presenting this just as an idea, and if it suits I'm sure it could be tailored to your exact likes. To view the code used, right click on the sheet tab and select View Code.

    Selecting A1 will enable viewing all four tables, which may be handy from time to time, updating etc.

    Andrew C

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

    Re: Display Table Title in Freeze Panes (XP / 2003)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Display Table Title in Freeze Panes (XP / 2003)

    An alternate to Andrew's if you don't mind the selecting, has them only "indirectly" on the same page. A1 contains datavalidation selection to select the table (you could use a FORMS or control toolbox object if desired for more control). Each of the table's ranges in "IBNR 2002-12" is named and the data validation picks the name. Only the one chosen table is displayed

    No scrolling at all, not even selecting places on the sheet. It is also done with no coding (no macro warning) only using formulas to fill in the table...

    Steve

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

    Re: Display Table Title in Freeze Panes (XP / 2003)

    Jamie has a wealth of choices now! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Table Title in Freeze Panes (XP / 2003)

    Hans, Andrew, and Steve,

    Thank you. As always, you've all given me some excellent options and, also, some ammunition to battle the politicos who "just don't get it." I'll let you know how things turn out.

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Table Title in Freeze Panes (XP / 2003)

    Hans,

    Your code works beautifully. I added a call in the Workbook_Open event, as you suggested and demoed it on a sample of the full worksheet using a 3 second refresh rate. Everybody loves it. You made me a hero, but I'm giving you the credit.

    Thank you, thank you, thank you,

Posting Permissions

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