Solved

Lock view  it accordingly

Posted on 2012-12-26
3
401 Views
Last Modified: 2012-12-26
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
Comment
Question by:Rayne
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38721012
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
 

Author Comment

by:Rayne
ID: 38721599
Thanks Brian
:)
Thats great
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38722177
Thanks, Rayne.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question