Adding VBA for multiple checkboxes -returns a compile error

Question

I have 70 check boxes on a sheet.

I would like each Checkbox - when clicked to fill a Rectangle around that Checkbox. If Checkbox is not clicked - the Rectangle will not be filled.

My issue is that I try to apply this code to each checkbox - but I am getting a compile error: "compile error ambiguous name detected boxcheck"

How do I prevent the compile error? Note: Each Checkbox will have it's own unique name (1-70), and each Rectangle will have its own Unique Name (1-70). This way each Checkbox should only fill the Rectangle that the VBA IF/THEN code references. I do NOT want 1 Checkbox to fill all rectangles.

Here is my code:

Sub BoxCheck()
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then
        ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.SchemeColor = 3
    End If
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = -4146 Then
        ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.SchemeColor = 1
    End If
End Sub

Have you checked the complete code to see if you have any duplicates checkbox names somewhere? — cybernetic.nomad
– cybernetic.nomad, Commented Oct 1, 2018 at 20:45
That can't be all of it. If BoxCheck is ambiguous, then it's declared in (at least) two places; remove all but one. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 1, 2018 at 20:55

Jchang43 · Accepted Answer · 2018-10-01 21:00:49Z

2

It could be throwing you an error if you have multiple subs within a module that are named the exact same. If you copy your original code, or the one below and simply replace the # for the rectangle and box number it pertains to, it might clear up the error.

Sub BoxCheck#()
    If ActiveSheet.Shapes("Check Box #").ControlFormat.Value = 1 Then
        ActiveSheet.Shapes("Rectangle #").Fill.ForeColor.SchemeColor = 3
    End If
    If ActiveSheet.Shapes("Check Box #").ControlFormat.Value = -4146 Then
        ActiveSheet.Shapes("Rectangle #").Fill.ForeColor.SchemeColor = 1
    End If
End Sub

Another option would be to put each BoxCheck into a different module, but that seems excessive, especially because you have 70 of them

edited Oct 1, 2018 at 21:00

answered Oct 1, 2018 at 20:43

Jchang43

8916 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2018-10-01 21:37:27Z

1

You can link all of your check boxes to a single sub and use Application.Caller to figure out which one called the method:

Sub BoxCheck()
    Dim shp, rectName as string
    Set shp = ActiveSheet.Shapes(Application.Caller)
    rectName = Replace(Application.Caller, "Check Box ", "Rectangle ")

    ActiveSheet.Shapes(rectName).Fill.ForeColor.SchemeColor = _
                        IIf(shp.ControlFormat.Value = 1, 3, 1)

End Sub

answered Oct 1, 2018 at 21:37

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

4 Comments

FreeMan Over a year ago

This is an excellent answer! It puts all the code doing the work in 1 place so that if there is ever a color change, or you want to have the rectangle outlined instead of filled, there's only 1 place to change it. Plus, TIL Application.Caller!

user9927197 Over a year ago

Thank you! However, when I use the code you provided.... I get a Run Time Error: '-2147024809 (80070057): The item with the specified name wasn't found. Then in the debugger it highlights the second part of your code in yellow. How should I fix? Note: I put the Check Box name and Rectangle name in the Quotations.

Tim Williams Over a year ago

Which line is the "second part"? The code assumes your checkbox/rectangle pairs are named in the same way "Check Box 1"/"Rectangle 1", "Check Box 2"/"Rectangle 2" etc

user9927197 Over a year ago

All good! Thanks! Works perfectly Great solution. I appreciate your help

Collectives™ on Stack Overflow

Adding VBA for multiple checkboxes -returns a compile error

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related