a little Excel macro help plz

Slackjaw190 · 4 Nov 2008 at 18:12

Hi

I am helping a friend with a spreadsheet as years ago I used to do little VB coding but as ive just realised I have forgotten it all. Its going well apart from the filter reset button. I have made a quick example to show you as I don’t want to show you the original.

The problem is if you hit the reset filter button, and you haven’t selected a filter you get the run time error which obviously we do not want. At the moment the code for the reset button is

Private Sub ToggleButton1_Click()

ActiveSheet.ShowAllData
End Sub

Private Sub CommandButton1_Click()
ActiveSheet.ShowAllData
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

.

What can I add to it so you cannot click the button unless a filter has been selected? I know this is simple but I really can’t remember how.

Any help much appreciated

Slackjaw190

DireEmblem · 4 Nov 2008 at 18:18

Easy fix,
record yourself removing autofilters, and then select the row and autofilter again to put it back on.

Done

Might add the code later if I can be bothered

Slackjaw190 · 4 Nov 2008 at 18:30

I thought that is what we did. We can reset the filters but i want to disable the button when a filter is not selected so you cant hit it and get the error. This is annoying cuz i know im missing something simple here

DireEmblem · 4 Nov 2008 at 19:09

No what is causing the error is when you are trying to reset the filters to null when they are already null.

What I meant was remove filters completely with the macro so there are none to choose from on the SS, and then reinstate them as below

Range("A13:E13").Select
Selection.AutoFilter ' Removes filter on row 13
Selection.AutoFilter ' Reinstates filter on row 13

Slackjaw190 · 4 Nov 2008 at 21:11

ah that works ty different way of doing it then i thought but probably better

Thanks a lot

Slackjaw190

SimonCHere · 5 Nov 2008 at 09:20

Just for future ref, and that you will have to change the code every time you move the filter range you could use:
(Just don't!

)

Code:

On Error Resume Next
ActiveSheet.ShowAllData

Or much better (Use This!)

Code:

with activesheet
  if .filtermode then
    .showalldata
  end if
end with

Simon