VBA Event Procedure Code – To Unfreeze Panes in Specific Worksheets

[wcm69]

I would like to replicate the Unfreeze Panes function using a VBA code. In other words
View >UnFreeze Panes > (All Rows and Columns)

However I only need this to apply for 2 specific worksheets in my workbook.

My workbook has multiple worksheets which currently has a VB code in TheWorkbook which applies the Freeze Panes to my entire workbook and I need to Unfreeze Panes (with an event procedure) in specific worksheets, but not the entire workbook.

I’ve recorded a Macro which works when I run it, but I haven’t been able to make run (automatically) as an Event Procedure and so far the codes I’ve found don’t seem to be working for me. The Recorded Macro Code I'm using is:

Sub UnfreezeCPanel()
'
' UnfreezeCPanel Macro
'
'
ActiveWindow.FreezePanes = False
ActiveWindow.SplitRow = 0
End Sub

I’m a VBA noobie so some help solving this issue (including a little instruction, i.e. where to place the code – I’m guessing it would go in the actual VBA Worksheets) would be greatly appreciated.

Thanks in advance

 

[JLGWhiz]

You would want to use the Worksheet_Activate event to do what you want.

Private Sub Worksheet_Activate()
ActiveWindow.FreezePanes = False
ActiveWindow.SplitRow = 0
End Sub

In each of the two sheets you want to unfreeze, right click the name tab of the sheet and then click 'View Code' in the pop up menu. Copy the above macro into the sheet code pane. When you have copied the code to both the sheet code modules, close the vb editor and the code will run each time you activate either of the worksheets. You could also modify the ThisWorkbook code to exclude those two sheets from the freeze action, but this is just as good.

 

[wcm69]

Hi JLGWhiz,

Thank you for taking the time to reply. I've tried the code and followed your instructions but when I try to run the code it returns a Compile error: 'Ambiguous name detected: Worksheet_Active' message.

I am already using a 'Private Sub Worksheet_Active()' code in the 2 sheets to activate/open a User Form, so I don't know if this may be causing the issue .

If you've got any idea how to get around the 'Compile error' issue I'd be very grateful.

Thanks in advance

 

[Michael M]

Put this

ActiveWindow.FreezePanes = False
ActiveWindow.SplitRow = 0

In the Open user form code BEFORE the user form opens

 

[JLGWhiz]

Put this

ActiveWindow.FreezePanes = False
ActiveWindow.SplitRow = 0

In the Open user form code BEFORE the user form opens

Good idea, Michael. Or in the UserForm_Initialize event.

 

[footoo]

https://www.mrexcel.com/forum/excel...ode-%96-freeze-pane-header-rows-1and-2-a.html

https://www.mrexcel.com/forum/excel...e-%96-unfreeze-panes-specific-worksheets.html

 

[wcm69]

Hi Michael M and JLGWizz,

Thank you both for your help. Unfortunately the code / Event is still not running automatically when I activate the 2 sheets I want to Unfreeze panes in. I'm still having to run the Macro to get it to activate. I was wondering if another way could be to use the 'IF and Then' statement with my existing code that freezes the other worksheets to Unfreeze the 2 sheets by using their Sheet Names

The code I've used (in Thisworkbook) to get the worksheets to Freeze panel is:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveWindow
.SplitColumn = 0
.SplitRow = 2
End With
ActiveWindow.FreezePanes = True

End Sub

Being new to VBA I don't know if this is a possible answer, but appreciate any help you can provide.

Thanks in advance

 

[Michael M]

This appears to be the 3rd time you have posted this question, at different times with responses from different users......I will no longer be following this thread !!

 

[Macropod]

wcm69: You have posted the same question at least three times. This contravenes Mr Excel's policy on Multiple-Posting in rule #12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
Kindly desist. Thread closed.