0

I am wondering if I could freeze a certain cells such as from L1 : P20 ? I had been searching everywhere and can't seems to find any solution. Anybody here can help me please?

Example

For example when i click on the command button. How am I able to freeze the whole thing at the right when i am scrolling down. I want the table to also follow me down when I scrolled downwards.

Community
  • 1
  • 1
user7334325
  • 35
  • 8

2 Answers2

0

Try the following to freeze right to your specified columns and play around hiding/unhiding the columns around as well as rows > 20:

Dim ws As WorkSheet
Set ws = ThisWorkBook.WorkSheets("MySheetName") ' <-- Change to your sheet name     
ws.Range("Q2").Select
ActiveWindow.FreezePanes = True

Then you could hide all rows to the right and below e.g. row 20:

Columns("Q:Q").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = True
Rows("21:21").Select
Range("C21").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = True

Last, but not least you can combine this answer with my second one using a UserForm.

T.M.
  • 9,436
  • 3
  • 33
  • 57
  • In case user7334325 didn't know if this was possible at all and thought maybe a VBA solution would be needed: this is the regular 'freeze panes' functionality also available from the ribbon :) Not to take any value away from your answer, @T.M. – Carl Colijn Sep 25 '17 at 09:24
  • @T.M. It works. But only for left to right scrolling. Why it would not follow me down when I am scrolling downwards? – user7334325 Sep 25 '17 at 09:41
  • @user7334325: maybe you selected a cell in row 1? All rows and columns before the cell you specify will get frozen, so e.g. C2 will freeze row 1 and columns A&B. You can experiment with this right in the Excel user interface by going to `View \ Window \ Freeze panes` in the ribbon. – Carl Colijn Sep 25 '17 at 09:45
  • @user7334325: Excel's 'freeze panes' functionality only allows you to fix the topmost x and/or leftmost x columns. Seeing your picture you want the range L1:P20 to sort of float over the sheet and stay in place while scrolling? To my knowledge, that is something Excel can't provide apart from showing the same sheet in multiple windows and resizing one of these windows to only show L1:P20. But that is not something you can force on the user I think if that is your goal. – Carl Colijn Sep 25 '17 at 10:33
  • For an alternative see also "Display a table in the UserForm by exctracting from Excel sheet" https://stackoverflow.com/questions/28190915/display-a-table-in-the-userform-by-extracting-from-excel-sheet – T.M. Sep 25 '17 at 10:57
0

Alternative

As an alternative you could create a UserForm and take advantage of the Worksheet_SelectionChange method - whenever you select a row you'll see the whole range in a separate list box of a userform. As a surplus the active row will be marked, if you are positioned within rows 1 to 20:

Step 1

Write this code into your WorkSheet module (MySheetName)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Not Application.Intersect(Target, Range("A1:P20")) Is Nothing Then
  ' Mark active row in list box
    If Not UserForm1.ListBox1 Is Nothing Then
      If Target.Row < UserForm1.ListBox1.ListCount Then
        UserForm1.ListBox1.ListIndex = Target.Row
      End If
    End If
    ' Show user form
    UserForm1.Show vbModeless

  End If

End Sub

Step 2

Create a UserForm with the following needed controls: ListBox1 and CommandButton1. Then use F7 to write this code into your newly created UserForm1 module

Option Explicit
Private Sub CommandButton1_Click()
   Me.Hide
End Sub


Private Sub UserForm_Initialize()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("MySheetName")

  Dim s     As String             ' range address string
  Dim rng   As Range              ' (target) range
' set range to L1:P20  
  s = "L1:P20"
  Set rng = ws.Range(s)

' define row source of list box via set range
  Me.ListBox1.RowSource = s

End Sub

Private Sub UserForm_Layout()
  Me.Caption = "My frozen Range L1:P20"

' Userform position in the top right corner
  Me.StartUpPosition = 0
  Me.Top = 0          ' or: Me.Top = Application.Top + ...
  Me.Left = Application.Left + Application.Width - Me.Width

With Me.ListBox1
  .ColumnCount = 5       ' five Columns L:P
  .ColumnHeads = True
  .ColumnWidths = .Width / .ColumnCount
End With
End Sub

Remark: This is only a simple example, which you can refine if you learn more about UserForms.

T.M.
  • 9,436
  • 3
  • 33
  • 57