Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

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

  1. #1
    Join Date
    1st January 2011
    Posts
    1

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,339

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,576

    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.

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  4. #4
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,576

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

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. vertical to horizontal
    By pytelium in forum EXCEL HELP
    Replies: 2
    Last Post: June 4th, 2006, 07:58
  2. Lookup Vertical and Horizontal
    By beuz in forum EXCEL HELP
    Replies: 16
    Last Post: February 21st, 2006, 17:53
  3. Horizontal to Vertical or Vertical to Horizontal?
    By docchang in forum EXCEL HELP
    Replies: 1
    Last Post: February 16th, 2005, 01:16
  4. horizontal & vertical alignment
    By ron centner in forum EXCEL HELP
    Replies: 2
    Last Post: October 20th, 2004, 06:21
  5. Vertical/Horizontal Analysis
    By tjtumlin in forum EXCEL HELP
    Replies: 3
    Last Post: March 5th, 2003, 01:51

Bookmarks

Posting Permissions

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