Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Freeze Rows

0

Hello,

Just wondering if it possible to freeze multi rows

for example i use to freeze top row only when we scroll

but i like to know if its possible to freeze top en left row from scrolling?

because can't find it how to do so.

Thanks

Answer
Discuss

Answers

0
Selected Answer

Ghost

To click top and leftmost row, click in cell B2 then go ribbon View/ Freeze Panes.../ Freeze Panes.

If you clicked in C3, top 2 and left 2 would be frozen (i.e. Excel freezes the cells above and to the left of the cell you Freeze Panes from).

It's harder to prevent controls from moving when scrolling (as you suggest in the discussion below) since Excel doesn't have a scrolling event. This code (put behind the sheet in question) would prevent an ActiveX control called "CommandButton1" from moving when the user moves between cells using the up/ down arrows (or restore it on hte sheet once a cell is selected after the scroll bar is moved):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

On Error Resume Next

With Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
    CommandButton1.Top = .Top + 50
    CommandButton1.Left = .Left + 10
End With

End Sub
See attached sample file. If you've frozen the pane, the bold values will be relative to the frozen row (/columns)

You could loop between shapes if you have several or repeat code lines per control if you have 2 or 3.

Hope this is what you wanted.

Discuss

Discussion

Hello John_Ru,
Ok, seems when i do that the left and top are locked but when i scroll
the left row still scrolls up? i want that also from moving
is that possible?

i have added a demo file
GhostofWanted (rep: 46) Nov 25, '21 at 4:53 am
Sorry but I don't know why you would want to scroll up or down (say) and have the left column not aligned with the scrolled rows (e.g. you scroll to row 50 but left value of row 2 is against row 50 data). What's your purpose please?
John_Ru (rep: 6092) Nov 25, '21 at 5:12 am
Seen your file but doesn't help me understand what you want (and there's no code)
John_Ru (rep: 6092) Nov 25, '21 at 5:15 am
Well, i like to keep the left row visible when they scroll because the left row will be like a menu with buttons, and when they scroll up its hard to click them.
And top row must be visible because row 2 will have some data 
like a title and amount.

hope this helps ;)
GhostofWanted (rep: 46) Nov 25, '21 at 5:16 am
Yes i know the file has no code because i use the freeze in sheet :)
not even know you can freeze with vba aswell
GhostofWanted (rep: 46) Nov 25, '21 at 5:19 am
See revised answer/ file.

Did you consider the alternative of adding a custom Toolbar to the Riboon (or customizing the Quick Access Toolbar)?
John_Ru (rep: 6092) Nov 25, '21 at 6:17 am
Hi John_Ru
looks funny , i like it
Thanks ;) i will play with the code to get my result ;)
GhostofWanted (rep: 46) Nov 25, '21 at 6:54 am
Okay, if you have non-ActiveX shapes, you might need to use the shape number to define then in the With statement, like:
Shapes(2).Top = .Top + 60
    Shapes(2).Left = .Left + 10
John_Ru (rep: 6092) Nov 25, '21 at 7:23 am
Thanks John_Ru
GhostofWanted (rep: 46) Nov 25, '21 at 7:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login