hobbiton73
03-01-2013, 09:36 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below to perform a number of tasks upon 'Workbook Close'.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long
Sheets("Input").Protect "handsoff", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
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
One of these tasks is to remove any filters that may have been set by the user, and this done via the following piece of code:
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
The problem I have is that when a user filters the records in the spreadsheet and then saves the file, rather than the filter being removed to show all the records, they receive a 'Run time error 13 mismatch' error and I've no idea why.
The sheet is password protected, so I'm not sure whether this is the issue, but I've been trying to get this to work for most of the afternoon after searching the internet, without any success.
I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.
Many thanks and lin dregards
Chris
I'm using the code below to perform a number of tasks upon 'Workbook Close'.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long
Sheets("Input").Protect "handsoff", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
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
One of these tasks is to remove any filters that may have been set by the user, and this done via the following piece of code:
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
The problem I have is that when a user filters the records in the spreadsheet and then saves the file, rather than the filter being removed to show all the records, they receive a 'Run time error 13 mismatch' error and I've no idea why.
The sheet is password protected, so I'm not sure whether this is the issue, but I've been trying to get this to work for most of the afternoon after searching the internet, without any success.
I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.
Many thanks and lin dregards
Chris