Loop counter in checkbox name

Question

I am trying to create a sheet which will have a checkbox in each non-empty line. To automatically adjust the number of checkboxes I created this macro:

Sub checkboxes()

Dim i As Integer

For i = 9 To 200
Set CurCell = ActiveSheet.Cells(i, 3)
    If CurCell.Value > 1 Then
        ActiveSheet.Shapes("CheckBox" & CStr(i)).Visible = True
    Else
        ActiveSheet.Shapes("CheckBox" & CStr(i)).Visible = False
    End If
    Next i

End Sub

I expect number of potential rows with data not greater than 200. Macro checks if value in column C for each line is >1, if true checkbox is visible, else it's hidden.

My problem is that I don't know how to put the loop counter "i" into Shape name - I got an error using code above. Can someone help?

Make sure your checkboxes are called CheckBox9, CheckBox10... — phil652
– phil652, Commented Jul 6, 2016 at 12:48
Checkboxes are named properly. The error I am getting is "The item with specified name wasn't found". — dzmkr
– dzmkr, Commented Jul 6, 2016 at 12:49
Agreed - When I add a new checkbox, the name comes through with spaces, eg. Check Box 1, so you calling out "Checkbox" & CStr(i) won't find anything, unless you manually changed the names of each checkbox. — TheGuyThatDoesn'tKnowMuch
– TheGuyThatDoesn'tKnowMuch, Commented Jul 6, 2016 at 12:50
Maybe one of them doesn't exist. Check the value of i when that error happens, and then go check that checkbox exists. — David G
– David G, Commented Jul 6, 2016 at 12:50

Community · Accepted Answer · 2017-05-23 10:28:54Z

I think this would be a more elegant solution.

This loops through all shapes on ActiveSheet and checks if they are a msoOLEControlObject (see here for more information on that matter).

Sub checkboxes()

Dim curCellValue as Variant
Dim i As Long
    For i = 1 To ActiveSheet.Shapes.Count
        If ActiveSheet.Shapes(i).Type = msoOLEControlObject Then
            curCellValue = ActiveSheet.Cells(i, 3).Value
            If curCellValue <> "" Then
                ActiveSheet.Shapes(i).Visible = True
            Else
                ActiveSheet.Shapes(i).Visible = False
            End If
        End If
    Next i
End Sub

So why is this "better"?

You don't have to "guess" how many values there will be.
If you ever change a name of a CheckBox this script will still be working.
This checks for empty cells.

Also note that I replaced Set CurCell = ActiveSheet.Cells(i, 3) with curCellValue = ActiveSheet.Cells(i, 3).Value. You don't need to Set an object in every iteration. Filling the variable suffices.

But: this will check for all msoOLEControlObjects which includes checkboxes, textboxes and the like.

HTH.

Collectives™ on Stack Overflow

Loop counter in checkbox name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related