Help with some VBA code for excel

  reddwarfcrew 19:30 14 Sep 03
Locked

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.

and


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

Can anyone help.

Thanks.

  VoG II 20:16 14 Sep 03

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

rng1.AutoFilter

End If

End Sub

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

  reddwarfcrew 20:18 14 Sep 03

Your help is appreciated.

  VoG II 20:33 14 Sep 03

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 03

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 03

I was just going to type that.

  VoG II 21:02 14 Sep 03

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

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

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…