samedi 17 décembre 2016

vba select /unselect all checkboxes with name x in spreadsheet?

I am trying to select/deselect all checkboxes with the name 'print' on a spreadsheet if another checkbox 'print1' is checked.

I am creating my checkboxes using the following code as part as a for each loop.

For Each objFile In objFolder.Files


If DatePart("ww", objFile.DateCreated, vbMonday, vbFirstFourDays) = Range("H7").Value Then


'print file PG
    Cells(i + 13, 1) = Range("T7").Value
    'print file Month
    Cells(i + 13, 5) = Range("H7").Value

    'print file Year
    Cells(i + 13, 9) = Range("B7").Value

    'print file name
    Cells(i + 13, 13) = objFile.Name

    'print file path
    Cells(i + 13, 18) = "=hyperlink(""" & objFile.Path & """)"

     'add action box 1
    ActiveSheet.CheckBoxes.Add(Cells(i + 13, 27).Left, Cells(i + 13, 27).Top, Cells(i + 13, 27).Width, Cells(i + 13, 27).Height).Select

    With Selection
    .Name = "print"
        .Caption = ""
        .Value = xlOff '
        .LinkedCell = Cells(i + 13, 27)
        .Display3DShading = False
            End With

This creates as many checkboxes with the name 'print' as required.

I also have a unique checkbox named 'print1'. This checkbox has a macro assigned to it called set_print. The macro should trigger when the user checks / unchecks this checkbox and should check/uncheck all of my other checkboxes named 'print'. TO do this I am using the following code:

Sub set_print()
If ActiveSheet.CheckBoxes("print").Value <> xlOn Then
ActiveSheet.CheckBoxes.Value = xlOn
ActiveSheet.Shapes("Search1").TextFrame.Characters.Text = "Print"
Else
ActiveSheet.CheckBoxes("print").Value = xlOff
ActiveSheet.Shapes("Search1").TextFrame.Characters.Text = "Search"
End If
End Sub

For some reason, only one of my checkboxes is checked. I am not sure what I am doing wrong, please can someone show me?




Aucun commentaire:

Enregistrer un commentaire