Auto Filter Macro

[Guest]

Greetings,
I have created a button on a worksheet with the following code:
Sub Macro4()
ActiveSheet.ShowAllData
End Sub

When I have the Autofilter active for a column(s), I just press this button
and the filter is taken off. No problems. However if I press the button
when the Autofilter is not filtering any of the columns, I get a debug error.
How do I tell the computer that if I press the button while the Autofilter
is already off, don't do anything? Thanks.

 

[Guest]

Try this:

Sub Macro4()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

 

[Dave Peterson]

Another way is to just check first:

With Activesheet
If .FilterMode Then
.ShowAllData
End If
End With

 

[Dave Peterson]

I'm not sure what error you're writing about (in xl97), but I agree that you
should use that "on error resume next" sparingly. And turn it back to your
error handler (or goto 0) right after the code that may cause the error.

 

[Guest]

I didn't realize that if I didn't offer a disclaimer on using "On Error
Resume Next" that it would be such an issue. So here it is: "On Error
Resume Next" is the quick and dirty solution to your problem. Do not use
this if you are performing brain surgery with excel. Otherwise it will work
just fine.

 

[Dave Peterson]

I've gotten that error (one or two times <vbg>). But none that I could
attribute to an "on error resume next" line.

#2. I use xl2003 and don't use many dialog sheets, but I almost always have the
forms toolbar, drawing toolbar, and control toolbox toolbar visible. As a test,
I added a dialog sheet and then swapped to the VBE. Nothing bad happened.

#3. I don't recall ever having excel crash when I had range names that had
errors. (My code crashed--but not excel.

#4. I've never seen excel crash because of this, either.

I've found that excel crashes with I do something wrong with an API function or
maybe even a corrupt workbook. Or other things that I couldn't pinpoint
(non-reproducible types).

 

[Dave Peterson]

When I was running xl97, I'd have those toolbars showing, too. I don't recall
any problems with corrupted workbooks. But that's just my experience. Your
mileage may vary.