loop through checkboxes

hi everyone, i'm trying to make a macro that loops through all of the checkboxes and if a checkbox is unchecked, it loops through all the cells in the column F, if the cell's value equals the checkbox's caption, then hide the entire row. below is the code i used, but the problem is it wouldn't always hide the row with the cell "GR A"

code :
Sub addcheckboxes()




For Each ws In ActiveWorkbook.Worksheets


If ws.Index > 3 Then
ws.Activate


Range("N1").Value = "Groupes"


ActiveSheet.checkboxes.Add(Left:=Range("N2").Left, Top:=Range("N2").Top, Width:=Range("N2").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR A"
End With
ActiveSheet.checkboxes.Add(Left:=Range("N3").Left, Top:=Range("N3").Top, Width:=Range("N3").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR B"
End With
ActiveSheet.checkboxes.Add(Left:=Range("N4").Left, Top:=Range("N4").Top, Width:=Range("N4").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR C"
End With
ActiveSheet.checkboxes.Add(Left:=Range("N5").Left, Top:=Range("N5").Top, Width:=Range("N5").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR D"
End With
ActiveSheet.checkboxes.Add(Left:=Range("N6").Left, Top:=Range("N6").Top, Width:=Range("N6").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR E"
End With
ActiveSheet.checkboxes.Add(Left:=Range("N7").Left, Top:=Range("N7").Top, Width:=Range("N7").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR F"
End With
ActiveSheet.checkboxes.Add(Left:=Range("N8").Left, Top:=Range("N8").Top, Width:=Range("N8").Width, Height:=Range("A1").Height).Select
With Selection
.Caption = "GR G"
End With


End If
Next ws


End Sub


Sub CheckboxLoop()


For Each ws In ActiveWorkbook.Worksheets


If ws.Index > 3 Then
ws.Activate


Dim LR As Long, i As Long
Dim cb As Shape
Application.ScreenUpdating = False




'Loop through Checkboxes
For Each cb In ActiveSheet.Shapes
If cb.Type = msoFormControl Then
If cb.FormControlType = xlCheckBox Then
If cb.ControlFormat.Value = xlOff Then
'Do something if not checked...
With ActiveSheet
LR = .Range("E" & Rows.Count).End(xlUp).Row
For i = 2 To LR
cb.Select
.Rows(i).Hidden = .Range("E" & i).Value = Selection.Characters.Text

Next i
End With
End If
End If
End If
Next cb
End If
Next ws

Application.ScreenUpdating = True
End Sub




thank you in advance

Code:

---

Sub addcheckboxes()
For Each ws In ActiveWorkbook.Worksheets
  If ws.Index > 3 Then
    'ws.Activate 'not needed
    With ws
      .Range("N1").Value = "Groupes"
      myArry = Array("GR A", "GR B", "GR C", "GR D", "GR E", "GR F", "GR G")
      For Each cll In .Range("N2:N8")
        With .CheckBoxes.Add(Left:=cll.Left, Top:=cll.Top, Width:=cll.Width, Height:=Range("A1").Height) 'height dependent on cell A1 and not row of checkbox?!
          .Caption = myArry(i)
        End With
        i = i + 1
      Next cll
    End With
  End If
Next ws
End Sub

Sub CheckboxLoop()
Dim LR As Long, i As Long
Dim cb As Shape
For Each ws In ActiveWorkbook.Worksheets
  If ws.Index > 3 Then
    'ws.Activate 'not needed
    With ws
      Application.ScreenUpdating = False 'could move this line to above: For each ws in…
      LR = .Range("E" & Rows.Count).End(xlUp).Row
      .Range("1:" & LR).EntireRow.Hidden = False
      'Loop through Checkboxes
      For Each cb In .Shapes
        If cb.Type = msoFormControl Then
          If cb.FormControlType = xlCheckBox Then
            If cb.ControlFormat.Value = xlOff Then
              'Do something if not checked...
              mytxt = cb.OLEFormat.Object.Text
              For i = 9 To LR 'bear in mind your checkboxes are on the first 9 rows so keep them visible all the time?
                If .Range("E" & i).Value = mytxt Then .Rows(i).Hidden = True
              Next i
            End If
          End If
        End If
      Next cb
    End With
  End If
Next ws
Application.ScreenUpdating = True
End Sub

---

Thank you so much for answering, but i fixed it !! :D
although i have another problem
here's the code, it works fine but it counts the hidden rows too :/ can you fix it please ?

Sub NumberColumn()
Dim ws As Worksheet
Dim lastRow As Long


For Each ws In ActiveWorkbook.Worksheets
If ws.Index > 3 Then
ws.Activate




Range("F1").Select
Selection.AutoFill Destination:=Range("F1:G1"), Type:=xlFillDefault
Range("F1:G1").Select
Range("G1").Value = "Numéro d'examen"


' get the last row from column A that has a value


lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row


' use the last row to determine how far down to extend the formula
ws.Range("G2:G" & lastRow).Formula = "=row()-1"
End If
Next
End Sub

update:

Code:

---

Sub addcheckboxes()
Dim ws, myArry, cll, i
For Each ws In ActiveWorkbook.Worksheets
  If ws.Index > 3 Then
    'ws.Activate 'not needed
    With ws
      .Range("N1").Value = "Groupes"
      myArry = Array("GR A", "GR B", "GR C", "GR D", "GR E", "GR F", "GR G")
      For Each cll In .Range("N2:N8")
        With .CheckBoxes.Add(Left:=cll.Left, Top:=cll.Top, Width:=cll.Width, Height:=Range("A1").Height)  'height dependent on cell A1 and not row of checkbox?!
          .Caption = myArry(i)
        End With
        i = i + 1
      Next cll
    End With
  End If
Next ws
End Sub
Sub CheckboxLoop()
Dim LR As Long, i As Long, ws, mytxt
Dim cb As Shape
For Each ws In ActiveWorkbook.Worksheets
  If ws.Index > 3 Then
    'ws.Activate 'not needed
    With ws
      Application.ScreenUpdating = False  'could move this line to above: For each ws in…
      LR = .Columns("E").Find("*", , xlFormulas, xlPart, , xlPrevious, , , False).Row  'if rows are hidden .End(xlup) won't find them
      .Range("1:" & LR).EntireRow.Hidden = False
      'Loop through Checkboxes
      For Each cb In .Shapes
        If cb.Type = msoFormControl Then
          If cb.FormControlType = xlCheckBox Then
            If cb.ControlFormat.Value = xlOff Then
              'Do something if not checked...
              mytxt = cb.OLEFormat.Object.Text
              For i = 9 To LR  'bear in mind your checkboxes are on the first 9 rows so keep them visible all the time?
                If .Range("E" & i).Value = mytxt Then .Rows(i).Hidden = True
              Next i
            End If
          End If
        End If
      Next cb
    End With
  End If
Next ws
Application.ScreenUpdating = True
End Sub

---

Since you're using the .checkboxes to add the checkboxes why don't you use the same for checking them?:

Code:

---

Sub CheckboxLoop2()
Dim LR As Long, i As Long, cb, mytxt, ws
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
  If ws.Index > 3 Then
    'ws.Activate 'not needed
    With ws
      LR = .Columns("E").Find("*", , xlFormulas, xlPart, , xlPrevious, , , False).Row  'if rows are hidden .End(xlup) won't find them
      .Range("1:" & LR).EntireRow.Hidden = False
      'Loop through Checkboxes
      For Each cb In .CheckBoxes
        If cb.Value <> 1 Then
          'Do something if not checked...
          mytxt = cb.Caption
          For i = 9 To LR  'bear in mind your checkboxes are on the first 9 rows so keep them visible all the time?
            If .Range("E" & i).Value = mytxt Then .Rows(i).Hidden = True
          Next i
        End If
      Next cb
    End With
  End If
Next ws
Application.ScreenUpdating = True
End Sub

---

re: "lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row"

use the same technique I used in the macros above.

Do have a read of http://www.excelguru.ca/content.php?184 , it is relevant to you.

I'm really sorry, and i appreciate the help especially because i've posted somewhere else like 2 days ago and no one had answered until you did, i'm not used to this i'm still learning lol thank you again.
Also i'm a vba "newbie" i don't know much about it, this is why my questions may seem easy for you.
Concerning the rows count, i need to do it after hiding the rows so i can number them, i need to give each student an exam number starting from 1 and depending on which class is having the exam ( Gr a or b or c etc..) this is what the checkbox loop is for. The code above ( NumberColumn sub ) counts the hidden rows also, i just need it to only count the visible rows.

depending on what you want, try:

Code:

---

    ws.Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Formula = "=row()-1"

---

or:

Code:

---

    i = 1
    For Each cll In Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Cells
    cll.Value = i
    i = i + 1
    Next cll

---

hi again, the addcheckboxes sub didn't work ( subscript out of range )
Also i tried the NumberColumn sub with the lines you gave me and it didn't work too :( thank you anyway

All work fine here.

The second option you gave me finally worked, i didn't know how to make it work at first, but when rows are empty the range "Numéro d'examen" gets the value 1, i'm trying to fix it now
Sub NumberColumn()
Dim ws As Worksheet
Dim lastRow As Long


For Each ws In ActiveWorkbook.Worksheets
If ws.Index > 3 Then
ws.Activate


'Name the range F1


Range("F1").Select
Selection.AutoFill Destination:=Range("F1:G1"), Type:=xlFillDefault
Range("F1:G1").Select
Range("G1").Value = "Numéro d'examen"


'Number the columns


lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
i = 1
For Each cll In Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Cells
cll.Value = i
i = i + 1
Next cll
End If
Next
End Sub

Code:

---

lastRow = application.max(ws.Range("A" & ws.Rows.Count).End(xlUp).Row,2)

---