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

How to watch the World Cup for free on TV and online

Best of the Grad Shows 2018: Middlesex University art, illustration and graphic design

Best Mac tips, tricks and timesavers

Comment identifier le modèle de son iPad ?