Help with some VBA code for excel

  reddwarfcrew 19:30 14 Sep 2003

I want to add some VBA code with does the following when a button is pressed:

if the autofilter is on then switch it off, otherwise do nothing.


if autofilter is off then switch it on and filter the area called 'database', by column 4 for fields with 'x'

Can anyone help.


  VoG II 20:16 14 Sep 2003

For question 1 get your button to run test:

Sub test()

Dim tempR As Range

Set tempR = ActiveSheet.UsedRange

Call switch(tempR)

End Sub

Sub switch(rng1 As Range)

'check to see if AutoFilter is on

If rng1.Parent.AutoFilterMode Then

' Filter is applied - next line turns it off


End If

End Sub

I'm thinking about the second question but don't expect a quick response!

  reddwarfcrew 20:18 14 Sep 2003

Your help is appreciated.

  VoG II 20:33 14 Sep 2003

This is how to do the filtering. Using code from my previous post you should be able to put the whole lot together:

Sub willitwork()

Range("database").AutoFilter Field:=4, Criteria1:="x"

End Sub

Thanks for your question, I've learnt something tonight!

  reddwarfcrew 20:44 14 Sep 2003

I'll try and put it into practice tomorrow.

The second bit is the same as what I'd got by recording a macro, but I had no idea how to do the first part.

Thanks again.

  powerless 20:50 14 Sep 2003

I was just going to type that.

  VoG II 21:02 14 Sep 2003

Nah, I copied and pasted it as per (not)!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

How Sketch and InVision have revolutionised our design workflow

The best tech gifts for Christmas 2017

Les meilleurs jeux de société (2017)