VBA code to Freeze/Unfreeze Panes based on a Cell Value

[lilydias]

Hi,

I have a sheet where it would be handy to have/not have frozen panes depending on how you're filling it out. Other people without excel experience will also be using this sheet, though.

I have a cell (x) with data validation that has two options: Freeze Panes/Unfreeze Panes. I'd like to insert a VBA code where if cell X equals "Freeze Panes", that rows 1 to 16 will be frozen. And if cell X says "Unfreeze Panes", that no rows will be frozen.

Anyone?

Thank you very much!

 

[TheMathLab]

Hi Lilydias.
Pop this into a module:

Sub FreezePanes()

    With ActiveWindow
    .SplitRow = 16
    .FreezePanes = True
    End With
    
End Sub

To figure out how to do this, I recorded a macro of me splitting and freezing the panes, then looked at the code and deleted whatever I felt I didn't need.

Hope that helps!

 

[TheMathLab]

In a module, I put this code:

Sub FreezePanes()


    With ActiveWindow
        .SplitRow = 16
        .FreezePanes = True
    End With
    
End Sub


Sub unFreezePanes()


    With ActiveWindow
        .FreezePanes = False
        ActiveWindow.SplitRow = 0
    End With
    
End Sub
[\CODE]

Then for it to take effect, pop this into your Sheet code:
[CODE]


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
        Set rng = Target.Parent.Range("F2")
            If Target.Count > 1 Then Exit Sub
            If Intersect(Target, rng) = "Freeze" Then
            Call FreezePanes
            Else
            Call unFreezePanes
            End If
            
End Sub

 

[lilydias]

Thank you! I'll try this out!