VBA Show All Data

[hobbiton73]

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'.

[vba]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[/vba]
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:

[vba] If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If[/vba]
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

[Simon Lloyd]

On which line do you get the error?

[hobbiton73]

Hi @Simon Lloyd, thank you for taking the time to reply to my post.

I don't get the error on a specific line, but when I omit this code:

[VBA]If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If[/VBA]

The user no longer receive the error message.

Many thanks and kind regards

Chris

[Simon Lloyd]

Try unprotecting the the sheet prior to filtering then protecting again afterwards. Are all the users using the same version of Excel?

[Zack Barresse]

If all data is showing, that line will error out.

Also, as Simon states, you can't perform this action on a worksheet that has been protected, unless you protect with the ability to filter. It's easiest just to trap for the error...

[vba] Dim WS As Worksheet
Dim EndRow As Long
Set WS = Me.Worksheets("Input")
WS.Protect "handsoff", UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
If WS.AutoFilterMode = True Then
On Error Resume Next
Err.Clear
WS.ShowAllData
On Error GoTo 0
If Err.Number <> 0 Then
'filter was already removed
End If
End If

If WS.Range("B7").Value = vbNullString Then Exit Sub
EndRow = WS.Range("B7").End(xlDown).Row - 1
WS.Range("B7:AG" & EndRow).Sort Key1:=WS.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
[/vba]

Edit: added AllowSorting

HTH

[hobbiton73]

Hi @Zack Barresse, thank you very much for taking the time to reply to my post and for the solution.

I did try your solution, and at first, the 'Run time error 13' was activated when activating the filter.

I then had a think about this, and realised that the cause of the error was that they didn't have the 'Analysis toolpack' add in activated.

As soon as I added a line of code to activate this through VBA, your code worked great.

Many thanks and kind regards

Chris

[hobbiton73]

Hi @Simon Lloyd, thank you very much for this.

As per my reply to @Zack Barresse, the whole issue was caused by the 'Analysis Toolpack' not being activated.

Once again, many thanks for your help.

Kind regards

Chris