工作表类的Excel VBA - ShowAllData方法失败
工作表类的Excel VBA - ShowAllData方法失败
提问于 2015-06-16 13:41:51
我已经将适当的记录输入自动化到我用作数据库的表中，而当对表进行过滤时，输入就不能工作了。
因此，在每次输入记录之前，我都要对DataBase进行反筛选。
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但现在，它仍然停留在Sheets("DB").ShowAllData上说：
工作表类失败的ShowAllData方法
因为桌子已经没有过滤了..。
我不知道是否更好地使用像On Error Resume Next这样的错误处理程序，也不知道如何检测是否存在过滤器或无。
任何指点都欢迎！
回答 3
Stack Overflow用户
回答已采纳
发布于 2016-09-28 10:17:48
下面是我使用的工作解决方案：
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此外，我还编写了一个更有效的版本，以便在命名为Ranges的情况下易于重用：
如何使用：
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和专用函数进行优化(如果您有大表的话)：
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 FunctionStack Overflow用户
发布于 2015-06-16 16:20:53
如果使用Worksheet.AutoFilter.ShowAllData而不是Worksheet.ShowAllData，则不会在未筛选任何内容时抛出错误。
这假设为Worksheet.AutoFilterMode = True，因为否则您将得到一个关于AutoFilter不是对象的错误。
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 SubStack Overflow用户
发布于 2016-11-01 12:54:02
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
For Each f In .AutoFilter.Filters
If f.On Then .ShowAllData: Exit For
Next页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30869361
复制相关文章
相似问题
腾讯云开发者
