Announcement

Collapse
No announcement yet.

Freeze top row and first column from both horizontal and vertical scrolling

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Freeze top row and first column from both horizontal and vertical scrolling

    Hi all,

    I've frozen columns A:B and row A1 using the built-in Excel freeze panes function and it works perfectly. However, I would like to additionally freeze columns A:B from vertical scrolling so that when the user scrolls the sheet, all that moves is columns C onwards less the first row (think frames on a webpage).

    Is this possible using VBA? Any advice would be appreciated.

    Thank you.

  • #2
    Re: Freeze top row and first column from both horizontal and vertical scrolling

    Excel will not let you dissociate rows or columns. It insists that every thing that looks like its on the same row IS on the same row

    Comment


    • #3
      Re: Freeze top row and first column from both horizontal and vertical scrolling

      How about a solution with a scroll bar? You could fix the scroll area with the .ScrollArea property of the worksheet, and use Index formula to fetch the column heading based on the value of the scroll bar. Attached an example.

      Code:
      Sub scrHeading_Change()
      
          ActiveWindow.ScrollColumn = Worksheets("Sheet1").ScrollBars("scrHeading").Value + 3
          
      End Sub
      
      Sub Auto_Open()
      
          With ThisWorkbook.Worksheets("Sheet1")
              .ScrollArea = "$1:$1"
              .ScrollBars("scrHeading").Value = 0
              scrHeading_Change
              Application.Goto .Cells(1)
          End With
          
      End Sub
      Attached Files
      Cheers,

      S M C

      Click To Read: How To Use Tags In Your Threads/Posts
      Please take time to read Forum Rules before posting
      Message To Cross Posters

      Comment


      • #4
        Re: Freeze top row and first column from both horizontal and vertical scrolling

        By the way, change scroll area to
        Code:
        .ScrollArea = "$1:$40"
        where 40 is the maximum screen view area, so that users can select those cells.
        Cheers,

        S M C

        Click To Read: How To Use Tags In Your Threads/Posts
        Please take time to read Forum Rules before posting
        Message To Cross Posters

        Comment

        Working...
        X