loop through checkboxes

[jihanebd]

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

[p45cal]

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

[jihanebd]

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

[p45cal]

update:
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?:
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.

[p45cal]

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

[jihanebd]

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.

[p45cal]

depending on what you want, try:
ws.Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Formula = "=row()-1"
or:

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

[jihanebd]

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

[p45cal]

All work fine here.

[jihanebd]

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

[p45cal]

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