Prevent ShowAllData Method from failing

[JimAA]

Hi,
I have an Excel 2007 worksheet that has autofilters and macros assigned to
option buttons in a Group Box that carry out some of the filtering. One
button is the ShowAllData button that removes the filter. If the ShowAllData
button is selected and then selected again (before any other button in the
group is selected) I get a Run Time Error that the ShowAllData method of
worksheet class failed. How do I prevent this error? I'm very new to VB.
The code below is what I'm having trouble with.
Thanks,
Jim

Sub UnhideBlanks()
'
' UnhideBlanks Macro
'
ActiveSheet.Unprotect Password:="sivle"
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.ShowAllData
ElseIf ActiveSheet.AutoFilterMode = False Then
Else
ActiveSheet.Protect Password:="sivle", DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End If

End Sub

 

[Dave Peterson]

With ActiveSheet
.unprotect ...
If .FilterMode Then
.ShowAllData
End If
.protect...
end with

 

[OssieMac]

Hi Jim,

AutoFilterMode tests if AutoFilter is applied to the worksheet (Like
AutoFilter drop downs are present)

FilterMode test if a filter has actually been set

Sub UnhideBlanks()
'
' UnhideBlanks Macro
'
ActiveSheet.Unprotect Password:="sivle"
If ActiveSheet.AutoFilterMode = True Then
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
ElseIf ActiveSheet.AutoFilterMode = False Then
Else
'ActiveSheet.Protect Password:="sivle", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End If

End Sub

 

[OssieMac]

Hi again Jim,

The following method of testing both AutoFilterMode and FilterMode together
in the one If statment also works.

Note there is no need to use = True because it is understood. (If testing
for False then you use = False)

Also I think your use of ElseIf and Else is not correct. I think the Else
part will never be processed under any conditions.

Note the space and underscore at the end of a line is a line break in an
otherwise single line of code. (I used them extensively when posting here
because it prevents unwanted line breaks in the post and the code can be
copied directly into your project.

Sub UnhideBlanks()
'
' UnhideBlanks Macro
'
ActiveSheet.Unprotect Password:="sivle"
If ActiveSheet.AutoFilterMode _
And ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

ElseIf ActiveSheet.AutoFilterMode = False Then
ActiveSheet.Protect Password:="sivle", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowSorting:=True, _
AllowFiltering:=True
End If

End Sub

 

[JimAA]

Thanks Dave and OssieMac, I tried both solutions and they did exactly what I
wanted.