Formatting using the hide feature

Posted by DHatchell on January 28, 2002 8:31 PM

I have a list of names in rows on sheet 1 with check boxes next to each name. On sheet two I have the same list of names in rows. I would like to make sheet 2 hide rows for the names that are unchecked on sheet one. Is it possible?

Please help!

Posted by Pangloss on January 29, 2002 4:18 AM

This is easier to set up if you just use a column to enter the check marks instead of using check boxes.

On Sheet1, assuming your names are in column B and column A is for the check marks, format the column A font as Marlett.
Check marks can then be entered in column A by the keystroke 'a'.

Then put this procedure in the Sheet1 code module :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Range([B1], [B65536].End(xlUp)).Offset(0, -1)
If Not Intersect(Target, rng) Is Nothing Then
For Each cell In Intersect(Target, rng)
With cell
If .Value = Chr(97) Then
Worksheets("Sheet2").Cells(.Row, .Column).EntireRow.Hidden = False
Else
Worksheets("Sheet2").Cells(.Row, .Column).EntireRow.Hidden = True
End If
End With
Next
End If
End Sub