Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Auto Filter Macro
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. |
#2
|
|||
|
|||
Try this:
Sub Macro4() On Error Resume Next ActiveSheet.ShowAllData End Sub "Snakeoids" wrote: 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. |
#3
|
|||
|
|||
Another way is to just check first:
With Activesheet If .FilterMode Then .ShowAllData End If End With Snakeoids wrote: 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. -- Dave Peterson |
#4
|
|||
|
|||
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 but stop on others) - if an error is trapped by the former, an error occuring during the latter can cause an Illegal Operation error in Excel 97 (and possibly Excel 2000 - I think this was fixed in a later version). BizMark |
#5
|
|||
|
|||
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. BizMark wrote: 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 but stop on others) - if an error is trapped by the former, an error occuring during the latter can cause an Illegal Operation error in Excel 97 (and possibly Excel 2000 - I think this was fixed in a later version). BizMark -- BizMark -- Dave Peterson |
#6
|
|||
|
|||
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. "Snakeoids" wrote: 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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Quote:
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 a 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 |
#9
|
|||
|
|||
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). BizMark wrote: Dave Peterson Wrote: I'm not sure what error you're writing about (in xl97), 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 a 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 -- BizMark -- Dave Peterson |
#10
|
|||
|
|||
Many of these problems seem to be less prevalent with later versions (the above conditions caused them repeatedly with xl97) - conditions 1 and 2 have caused me problems here at work on xl2003.
There is a bug which leavs the 'Forms' toolbar floating when switching from worksheet view to VBE view, and saving when this happens invariably corrupts the workbook. Many times when I've tried it with later versions as I've progressed through the versions, I've expected this problem to be resolved, but the same thing has happened time and time again, regardless. And this is on different types of PC, using different types of network software (Novell and NT Server, various versions) and at home with no network. BizMark Quote:
|
#11
|
|||
|
|||
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. BizMark wrote: Many of these problems seem to be less prevalent with later versions (the above conditions caused them repeatedly with xl97) - conditions 1 and 2 have caused me problems here at work on xl2003. There is a bug which leavs the 'Forms' toolbar floating when switching from worksheet view to VBE view, and saving when this happens invariably corrupts the workbook. Many times when I've tried it with later versions as I've progressed through the versions, I've expected this problem to be resolved, but the same thing has happened time and time again, regardless. And this is on different types of PC, using different types of network software (Novell and NT Server, various versions) and at home with no network. BizMark Dave Peterson Wrote: 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). BizMark wrote:- Dave Peterson Wrote:- I'm not sure what error you're writing about (in xl97),- 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 a 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 -- BizMark- -- Dave Peterson -- BizMark -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Filter Switches ... Maybe??? | Excel Discussion (Misc queries) | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) | |||
auto filter question | Excel Worksheet Functions | |||
The Auto Filter button lost the column specified option. | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |