Message Box Warning

[Fitzhay]

I have recorded two macros and attached them to buttons on the sheet. The first macro is for an advanced filter in place and the second is to show all the data again.

If someone clicks on the restore button BEFORE running the advanced filter the Microsoft Visual Basic box opens with runtime error '1004' displayed and the message "ShowAllData method of Worksheet class failed" and displaying three active buttons 'End', 'Debug' and 'Help"

The macro code is:

Sub Restore()

ActiveSheet.ShowAllData

End Sub

I would like a warning box to pop up on clicking the Restore button BEFORE running the filter to say

"you MUST click on the SEARCH button BEFORE clicking on the RESTORE button"

Any help gratefully received!

 

[Sal Paradise]

Sub Restore()

On Error GoTo Hell

ActiveSheet.ShowAllData

Hell:
MsgBox("you MUST click on the SEARCH button BEFORE clicking on the RESTORE button")

End Sub

 

[mikerickson]

ShowAllData is one of the buttons availiable from Tools>Customize>Customize Toolbars>Commands. It greys out when the sheet is unfiltered.

 

[Fitzhay]

Thanks Sal for the code. While it works it also brings the message box up when the sheet has been filtered. I guess I need something that checks if the sheet is filtered and then returns the message when it finds that it isn't filtered. Is this possible??

Thanks for the help.

 

[Yard]

Think there's just an Exit Sub required before the Hell: line.

 

[Fitzhay]

Thanks for that Yard. Did the trick nicely!

Thank you all for your help, I appreciate it.