Status: Solved
Priority: Medium
Security: Public
Views: 435
Last Modified: 2012-12-26

Lock view it accordingly

Hello All,

I have a VBA issue. Is it possible to toggle freeze-rows at the column headers of two data sets in the same sheet? Like:
if I put 1 in cell K2, then freeze panes happens at D5.
if I put 2 in cell K2, then freeze panes happens at D22.

Thank you
R
lockFreezeIt.xlsx

Rayne

Asked: 2012-12-26

Advertise Here

1 Solution

LVL 26

redmondbCommented: 2012-12-26

Hi, Rayne.

Please see attached. The code is...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range
Dim xChoice

If Intersect(Target, Me.Range("K2")) Is Nothing Then Exit Sub

xChoice = Me.Range("K2")
If (xChoice <> 1 And xChoice <> 2) Then Exit Sub    'Ignore values other than "1" and "2".

Application.ScreenUpdating = False
    
    Set xCell = ActiveCell                          'Remember where we are.
    
    ActiveWindow.FreezePanes = False
    If xChoice = 1 Then
        Me.Range("D5").Select
    Else
        Me.Range("D22").Select
    End If
    ActiveWindow.FreezePanes = True
    
    xCell.Select                                    'Back to where we came from.

Application.ScreenUpdating = True

End Sub