Auto Filter Macro

S

Snakeoids

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.
 
D

Dave Peterson

Another way is to just check first:

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

BizMark

Indeed. On Error Resume (Next) can be a nasty catch all.

Also - beware of using

On Error Resume (Next)
and
On Error Goto 0

in the same sub-routine (where you might want to ignore some errors bu
stop on others) - if an error is trapped by the former, an erro
occuring during the latter can cause an Illegal Operation error i
Excel 97 (and possibly Excel 2000 - I think this was fixed in a late
version).

BizMar
 
D

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.
 
S

StinkeyPete

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.
 
B

BizMark

StinkeyPete,

What I made was a valid point that people would do well to heed. That
is what this forum is about.

If you can't handle considered advice from other people with experience
which may supplement yours, I suggest you hang on to your personal
and/or sarcastic comments. They are not needed, not helpful and not
civil.

BizMark
 
B

BizMark

Dave said:
I'm not sure what error you're writing about (in xl97),
Click to expand...


I think millions of Excel users around the world are only too aware
what error message this is.

Surely you can't tell me you've NEVER had this?

"Microsoft Excel has performed an Illegal Operation and will be shut
down. If the problem persists, please contact the software vendor".

Or, in later versions,

"Microsoft Excel has encountered a problem and needs to close. We
apologise for any inconvenience. Do you want to submit an error
report?"

There are a myriad causes for this behaviour, some are consistently
repeatable and therefore trappable, and some are not. Any situation
which causes these to happen with any regularity, it is useful to
people to point out as it can prevent them from losing many man hours
or days of work. Some are:

1. Using On Error Resume Next then On Error Goto 0
2. Editing VBA code when the 'Forms' toolbar was open on a Dialogsheet
before switching to the VBE, then saving in the VBE
3. Operating on range names in macros when the name definitions contain
#REFs etc.
4. One user running macro in a workbook, another user having the same
workbook open as Read-Only, then the other user does 'Save As' to make
a copy - result - Illegal Operation/Encountered a Problem error occurs
for user running the macros.

There are many others that don't spring to mind straight away.
Sometimes this error cannot be explained.

However, I think StinkeyPete needs to realise that it needn't be an
issue just because somebody has issued a warning over his solution.

BizMark
 
D

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).
 
B

BizMark

Many of these problems seem to be less prevalent with later version
(the above conditions caused them repeatedly with xl97) - conditions
and 2 have caused me problems here at work on xl2003.

There is a bug which leavs the 'Forms' toolbar floating when switchin
from worksheet view to VBE view, and saving when this happen
invariably corrupts the workbook. Many times when I've tried it wit
later versions as I've progressed through the versions, I've expecte
this problem to be resolved, but the same thing has happened time an
time again, regardless. And this is on different types of PC, usin
different types of network software (Novell and NT Server, variou
versions) and at home with no network.

BizMark
 
D

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.
 
Top