hide/unhide rows using check box dependant on each rows value if true or false

[lbillie]

I have looked almost everywhere to find a macros for my active x check box and can't find one that I need. Your help would be awesome. I have a spread sheet that lists all the vendors. In row 5-154, Column V I have a list that states true or false. True = we will buy from this vendor. False = we won't buy from this vendor. I have 1 active x check box located in my upper left hand spread sheet. I would like the user to apply the check to the check box to show only the vendor's we will buy from. Hide the rows of vendors we won't buy from. If unchecked, it will show all vendors who fall in the category true or false.

 

[HalfAce]

If your checkbox is (still) named CheckBox1, then perhaps something like this.

Private Sub CheckBox1_Click()
  If CheckBox1 Then
    Range("V4:V154").AutoFilter Field:=1, Criteria1:="TRUE"
  Else
    ActiveSheet.AutoFilterMode = False
  End If
End Sub

(If it's named something different, then just change both instances of CheckBox1 to whatever you named it.)

Hope it helps.

 

[lbillie]

OH MY GOSH!!! This is exactly what I needed! Thank you so much!!!!

 

[HalfAce]

You're very welcome. Glad it helped.

 

[RFSAR]

Hi *First Post*, Is there a way of giving a range of rows a name space so that controls over them are dynamically updated when adding more rows to the sheet? for instance

ActiveSheet.Rows("237:253").EntireRow.Hidden = True

changed to something like

ActiveSheet.Rows("Module1").EntireRow.Hidden = True

This is for a software testing script, the software can be customized with additional modules so we need the script to only show rows that are pertinent to the customers system. The person working on the project before me has used row references so when more steps need to be added it is required to alter 400+ row references. If the groups of rows / information held within could be given a namespace additional rows being added to the sheet the show hide functions would be dynamic. Is this possible?

Hope this makes sense!

 

[RFSAR]

Haha, worked it out!!!

Ok so using the name box you can create a string reference for a group of cells/rows. the Syntax for referencing to this is like so

"ActiveSheet.Range("OinkOinkModuleTesting").EntireRow.Hidden = False"

Hope this helps someone somewhere :D