• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

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
0
Rayne
Asked:
Rayne
  • 2
1 Solution
 
redmondbCommented:
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
0
 
RayneAuthor Commented:
Thanks Brian
:)
Thats great
0
 
redmondbCommented:
Thanks, Rayne.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now