Loop Through Checkboxes

[Cruiser]

Goal: Loop through checkboxes one at a time with BringToFront.

I have a workbook in 2003 which has 1000 forms control checkboxes spread over 3 worksheets. When the sheet is protected, clicking on certain boxes does not check them, but rather the box above or below due to the close proximity of the checkboxes to one another. I have found that if I start at the top, right click one box at a time and "Order > Bring to Front", I am able to click on each box even when the sheet is protected. I would rather run a macro than do this 1000 times. Can someone help with a code that will loop through all boxes in order - something like For i = 1 to 1000 with maybe a "ZOrder msoBringToFront" ?

Thanks

 

[NARAYANK991]

Hi ,

I have not tested this ; see if it works :

Public Sub BringToFront()
           Dim chkbox As Object
           With ActiveSheet
                For Each chkbox In .CheckBoxes
                    .Shapes(chkbox.Name).ZOrder (msoBringToFront)
                Next
           End With
End Sub

Narayan

 

[Cruiser]

Thanks Narayan. Tried it, but that did not do the trick. But I was able to tweak it to make it work. Had to "bring to front" each checkbox in numerical order for it to work. Hear is the final code that does the trick:

Public Sub BringCbxToFront()
    Dim checkbox As Object
        Dim i As Integer
        On Error Resume Next
    With ActiveSheet
        For i = 1 To 1000
        .Shapes("Cbx " & i).ZOrder msoBringToFront
        Next
      End With
End Sub