Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
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" wrote in message ups.com... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
"davegb" wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
If you are not using Autofilter, what filters do you mean?
-- HTH RP (remove nothere from the email address if mailing direct) "davegb" wrote in message ups.com... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
I'm using the advanced filter. See my last message.
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace debug error box
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show All in Protected Sheet (was: Replace debug error box)
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 "davegb" wrote in message ups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Debug Error | Excel Discussion (Misc queries) | |||
Debug Error | Excel Discussion (Misc queries) | |||
Debug Error | Excel Discussion (Misc queries) | |||
down with the debug error | Excel Programming | |||
Debug Error | Excel Programming |