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
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
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.
Shapes(2).Top = .Top + 60
Shapes(2).Left = .Left + 10