Consulting

Results 1 to 11 of 11

Thread: loop through checkboxes

  1. #1
    VBAX Newbie
    Joined
    Aug 2016
    Posts
    5
    Location

    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

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,039
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Newbie
    Joined
    Aug 2016
    Posts
    5
    Location
    Thank you so much for answering, but i fixed it !!
    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

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,039
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,039
    Do have a read of http://www.excelguru.ca/content.php?184 , it is relevant to you.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Newbie
    Joined
    Aug 2016
    Posts
    5
    Location
    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.

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,039
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Newbie
    Joined
    Aug 2016
    Posts
    5
    Location
    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

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,039
    All work fine here.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    VBAX Newbie
    Joined
    Aug 2016
    Posts
    5
    Location
    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

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,039
    lastRow = application.max(ws.Range("A" & ws.Rows.Count).End(xlUp).Row,2)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •