Prevent ShowAllData Method from failing

J

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
 
D

Dave Peterson

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

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
 
O

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
 
J

JimAA

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top