Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Freeze Panes Question? (XP)

    Hello,

    If you please look at the attached XL file, you can see my attempt at Freezing Frames.
    I want the top & side heading to always appear irrespective of where you are on the sheet.

    But if I scroll down vertically, the side heading moves off the screen.
    And if I scroll horizontally, the top heading moves off the screen.

    How can I tweak this so I always have those heading fixed there no matter where I scroll in the sheet?

    Thanks,

    Bob.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Freeze Panes Question? (XP)

    Instead of freezing panes at cell B2, you'd have to freeze panes at cell K13. This means the entire range A1:J12 will remain visible as you scroll horizontally or vertically, which is probably not the effect you want. However your options are limited with Freeze Panes. As noted in "Microsoft Excel Help":

    <hr>1. To freeze the top horizontal pane, select the row below where you want the split to appear.
    To freeze the left vertical pane, select the column to the right of where you want the split to appear.

    To freeze both the upper and left panes, click the cell below and to the right of where you want the split to appear.

    2. On the Window menu, click Freeze Panes. <hr>
    There's no option in Excel to freeze the horizontal and vertical panes independent of each other.

    HTH

  3. #3
    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: Freeze Panes Question? (XP)

    This requires a macro.Add this code to the sheet object in VB.

    The size of the horizontal is determined by the height of row1. The length is hardcoded. It is yellow background, fontsize 14, bold, center aligned, all other formatting is the default for the textbox.
    The vertical is as wide as the first col and the height is hardcoded.It has a green background, fontsize 14, bold, center aligned, all other formatting is the default for the textbox.

    You will also have to change the "text" as desired. Modify the formatting as desired for the object in the code. Add other formatting as desired.

    <pre>Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim shp As Shape
    Dim sTBHor As String
    Dim sTBVert As String
    Dim wks As Worksheet

    sTBHor = "txtBxHorizontal"
    sTBVert = "txtBxVertical"

    Set wks = ActiveSheet
    On Error Resume Next
    wks.TextBoxes(sTBHor).Delete
    wks.TextBoxes(sTBVert).Delete
    On Error GoTo 0
    With ActiveWindow.VisibleRange
    Set shp = wks.Shapes.AddTextbox( _
    Orientation:=msoTextOrientationHorizontal, _
    Left:=wks.Cells(1, .Column).Left, _
    Top:=wks.Cells(1, .Column).Top, _
    Width:=423, _
    Height:=wks.Cells(1, .Column).Height)
    End With

    shp.Name = sTBHor

    With wks.TextBoxes(sTBHor)
    .Text = "My Top Heading is Here"
    .Interior.Color = vbYellow
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    With .Font
    .Bold = True
    .Size = 14
    End With
    End With

    With ActiveWindow.VisibleRange
    Set shp = wks.Shapes.AddTextbox( _
    Orientation:=msoTextOrientationUpward, _
    Left:=wks.Cells(.Row, 1).Left, _
    Top:=wks.Cells(.Row, 1).Top, _
    Width:=wks.Cells(.Row, 1).Width, _
    Height:=183)
    End With

    shp.Name = sTBVert
    With ActiveSheet.TextBoxes(sTBVert)
    .Text = "My Side Heading is Here"
    .Interior.Color = vbGreen
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    With .Font
    .Bold = True
    .Size = 14
    End With
    End With
    Set shp = Nothing
    Set wks = Nothing
    End Sub</pre>


    It uses 2 textboxes and redraws them when the selection is change. What the code does is to delete the previous 2 textboxes, then it creates a new one at the end of the corner of the visible range

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze Panes Question? (XP)

    Thank you both. Your replies helped solve the problem.

    Bob.

Posting Permissions

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