Replace debug error box

[davegb]

I'm creating a series of spreadsheets for basic XL users. To make it
simple, I've recorded macros to filter for specific sets of data they
want to see. Then creatd buttons on the spreadsheet to activate those
filters. All this works great.
I also created a "Show All" macro and button, to remove the filter and
show all records. The problem is, if I click "Show All", and all the
records are already showing, I get the macro debug screen. I want this
to be as simple and foolproof as possible. I know someone sooner or
later will click "debug", end up in the VBA editor, and somehow hose up
the code! How do I prevent the debug screen from popping up, and
replace it with a message box saying "All records are displayed" with
an OK button? I tried looking in other threads for the answer, but the
only one I found that applied was:

On Error GoTo Handler
Handler:
MsgBox "All Data is Displayes"

but the message box shows every time you press the button, not just
when the error occurs. How to I get the Message box to only appear when
the debug error occurs?

 

[Bob Phillips]

Dave,

This code

Columns("H:H").AutoFilter Field:=1

which is analogous to your Show All doesn't error for me. What does your
code look like?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[davegb]

Bob,
My code was:

ActiveSheet.ShowAllData

I just recorded it. I tried yours, and if fixed the problem. Thanks! I
tried it with column a instead of h, and i worked fine. Does it matter
at all what column you use?

 

[Bob Phillips]

Bob,
My code was:

ActiveSheet.ShowAllData

I just recorded it. I tried yours, and if fixed the problem. Thanks! I
tried it with column a instead of h, and i worked fine. Does it matter
at all what column you use?

Only insofar as it must refer to the column that you have set Autofilter on.

 

[davegb]

Since I'm not using Autofilter, I guess it doesn't matter.
I have found another issue. If I protect the spreadsheet, the filters
still work, but the remove filter button gives me a "Runtime Error
1004". It seems strange to me that I can filter, but not remove the
filter. I'm guessing it has to do with the method. I'm using a recorded
macro and using advanced filter to apply the filter.

Range("A1:G13").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("K1:K2"), Unique:=False

For the "Show All" button, I'm using a macro with

Columns("a:a").AutoFilter Field:=1

Is there a way to remove the filter that will run on a protected
worksheet?

 

[Bob Phillips]

If you are not using Autofilter, what filters do you mean?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[davegb]

I'm using the advanced filter. See my last message.

 

[davegb]

I fixed the problem. I cleared the filter in a way that XL allows in a
protected spreadsheet. I created an advanced filter with a criteria
range with all criteria blank! Works great.

 

[Guest]

I would like to be able to Show All in a protected sheet. I have
successfully implemented the suggestions in the thread below, but in addition
to Showing All, they CLEAR the Filters.

Anyone know of a way to Show All in a protected sheet without clearing the
filters?
TIA