hobbiton73
02-24-2013, 09:17 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below to perform a number of actions on the 'Workbook Close' event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long
Sheets("Input").Protect "password", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
ActiveWindow.FreezePanes = False
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
.Cells.EntireRow.Hidden = False
.Cells.EntireColumn.Hidden = False
If .Range("B7").Value = "" Then Exit Sub
'find last row of data
EndRow = .Range("B7").End(xlDown).Row - 1
.Range("B7:AG" & EndRow).Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The code works fine except for one area, where I'm trying to remove 'Freeze Panes' which is this line in my code:
ActiveWindow.FreezePanes = False
The workbook I use consists of a number of worksheets so the user may save the file on a sheet which doesn't have the Freeze Panes set, and hence will not be the 'ActiveWindow'.
Could someone perhaps tell me please whether it is possible to remove the Freeze Panes from a specific sheet, in this case "Input" or perhaps all of the worksheets in the workbook.
Many thanks and kind regards
Chris
I'm using the code below to perform a number of actions on the 'Workbook Close' event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long
Sheets("Input").Protect "password", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
ActiveWindow.FreezePanes = False
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
.Cells.EntireRow.Hidden = False
.Cells.EntireColumn.Hidden = False
If .Range("B7").Value = "" Then Exit Sub
'find last row of data
EndRow = .Range("B7").End(xlDown).Row - 1
.Range("B7:AG" & EndRow).Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The code works fine except for one area, where I'm trying to remove 'Freeze Panes' which is this line in my code:
ActiveWindow.FreezePanes = False
The workbook I use consists of a number of worksheets so the user may save the file on a sheet which doesn't have the Freeze Panes set, and hence will not be the 'ActiveWindow'.
Could someone perhaps tell me please whether it is possible to remove the Freeze Panes from a specific sheet, in this case "Input" or perhaps all of the worksheets in the workbook.
Many thanks and kind regards
Chris