Lock view  it accordingly

Rayne
Rayne used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
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

Open in new window

Regards,
Brian.lockFreezeIt-V2.xlsm
Rayne

Author

Commented:
Thanks Brian
:)
Thats great
redmondb

Commented:
Thanks, Rayne.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial