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? |
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 |