I have automated a proper record input into the table that I use as a database, and when the table is filtered the input don't work.
So I have code this to unfilter DataBase before every record input.
Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean
CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name
Sheets("DB").Activate
Sheets("DB").Range("A1").Activate
Sheets("DB").ShowAllData
DoEvents
Sheets(ActiveS).Activate
Application.ScreenUpdating = CurrScreenUpdate
End Sub
But now, it stays stuck on Sheets("DB").ShowAllData saying :
ShowAllData method of Worksheet Class failed
because the table is already unfiltered...
And I don't know if it is better to use an error handler like On Error Resume Next or how to detect if there is a filter or none.
Any pointers would be welcome!
If you use Worksheet.AutoFilter.ShowAllData instead of Worksheet.ShowAllData it will not throw the error when nothing is filtered.
This assumes that Worksheet.AutoFilterMode = True because otherwise you will get an error about AutoFilter not being an object.
Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean
CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name
Sheets("DB").Activate
Sheets("DB").Range("A1").Activate
Sheets("DB").AutoFilter.ShowAllData
DoEvents
Sheets(ActiveS).Activate
Application.ScreenUpdating = CurrScreenUpdate
End Sub
Here is the working solution I went with :
Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean
CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name
Sheets("DB").Activate
Sheets("DB").Range("A1").Activate
On Error Resume Next
If Sheets("DB").FilterMode = True Then Sheets("DB").ShowAllData
On Error GoTo 0
DoEvents
Sheets(ActiveS).Activate
Application.ScreenUpdating = CurrScreenUpdate
End Sub
And a more efficient version I wrote to be reusable easily with Named Ranges :
How to use it :
Private Sub TEST_UnFilter_Table()
Dim tB As Workbook, _
Sh As Worksheet
Set tB = ThisWorkbook
Set Sh = tB.Sheets("DB")
Call UnFilter_Table(Sh, "Db_Val")
End Sub
And the proper function with optimisation (if you have big tables) :
Public Function UnFilter_Table(ByRef SheetWithTable As Worksheet, ByVal RangeName As String) As Boolean
On Error GoTo ErrHdlr
Dim aWB As Workbook, _
ActiveSH As Worksheet, _
ScreenUpdateState As Boolean, _
StatusBarState As Boolean, _
CalcState As XlCalculation, _
EventsState As Boolean, _
DisplayPageBreakState As Boolean
Set aWB = ActiveWorkbook
Set ActiveSH = aWB.ActiveSheet
DisplayPageBreakState = ActiveSH.DisplayPageBreaks
ActiveSH.DisplayPageBreaks = False
With Application
ScreenUpdateState = .ScreenUpdating
StatusBarState = .DisplayStatusBar
CalcState = .Calculation
EventsState = .EnableEvents
.ScreenUpdating = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
SheetWithTable.Activate
SheetWithTable.Range(RangeName).Cells(1, 1).Activate
On Error GoTo 0
On Error Resume Next
If SheetWithTable.FilterMode Then SheetWithTable.ShowAllData
On Error GoTo 0
On Error GoTo ErrHdlr
DoEvents
ActiveSH.Activate
ActiveSH.DisplayPageBreaks = DisplayPageBreakState
With Application
.ScreenUpdating = ScreenUpdateState
.DisplayStatusBar = StatusBarState
.Calculation = CalcState
.EnableEvents = EventsState
End With
UnFilter_Table = True
On Error GoTo 0
Exit Function
ErrHdlr:
UnFilter_Table = False
Debug.Print "Error in unfiltering sheet " & SheetWithTable.Name & " !" & vbCrLf & _
"Error n° " & Err.Number & vbCrLf & _
Err.Description
End Function
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
For Each f In .AutoFilter.Filters
If f.On Then .ShowAllData: Exit For
Next
