Reading a checkbox value in Excel 2010 using VBA generates error 424

A Forms checkbox would be more likely named Check Box 1. You right-click a Forms checkbox and choose Assign macro to assign a macro to it--most likely on a regular module sheet.

An ActiveX checkbox would be more likely named CheckBox1, and would be associated with a formula like:
=EMBED("Forms.CheckBox.1","")
While in design mode (triangle and ruler icon on Developer toolbar is selected), you can rightclick an ActiveX checkbox and choose View Code to see its Click event sub on the code pane for the worksheet. The code looks like what you posted--but it won't work unless you put it on the code pane for the worksheet containing the checkbox.

You need to know exactly which type of checkbox you have.

If you truly have a Forms checkbox and have put the sub in a regular module sheet, then it should look like:

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes("Check Box 1").Value = 1 Then
       MsgBox ("Box checked")
   End If
End Sub

Select allOpen in new window

On my test workbook, the Forms checkbox is named "Check Box 1", so I had to replicate that in the sub.

Since many Forms checkboxes can be linked to the same sub, you may prefer this alternative construction:

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
       MsgBox (Application.Caller & " checked")
   End If
End Sub

Select allOpen in new window

Hi Byundt,

Thanks for your replies!

I double-checked and I am using Forms.  I followed your previous reply, specifically the first suggestion of your previous reply, and now have this:

Sub CheckBox3_Click()
  If ActiveSheet.CheckBoxes("CheckBox3").Value = 1 Then
       .
       .

Now I get this error on the If statement:

    Run-time error '1004':
    Unable to get the CheckBoxes property of the Worksheet class

Any further ideas on how to get a Form checkbox to work?  Again, I have one Forms checkbox on Sheet1 of the spreadsheet.  I've done nothing to enable/disable any specific worksheet.

Thanks again.

Dave

Dave,
Would you mind posting a workbook that illustrates the problem, along with the macro that you are trying to use?

Since the code is working at my end, I want to see if there is something I didn't anticipate about your workbook. If not, then it may be a problem with your particular install of Excel.

Have you tried rebooting?

Brad

Hi Brad,

I'm glad to post my Excel.  BTW and as I mentioned earlier, when my 5 line program didn't function at work, I went home (nearby), re-created it from scratch (i.e. I didn't bring a copy home with me), and it didn't work on my home computer either.  Both computers are Excel 2010.

I did reboot my work computer to no affect.

Attached please find:

  1. Book1.xlsm -- Contains a checkbox Forms Control in cell A1.  As you'll see, the
      following code is associated with this control (this is my original code, but I'll
      be happy to use any code that works):

               Sub CheckBox1_Click()
                  If CheckBox1.Value = True Then
                       MsgBox ("BoxChecked")
                  End If
               End Sub


  2. A screenshot showing my checkbox in cell A1 -- ExcelScreenshot.png

  3. A screenshot showing the Error 424 error message -- Error-424.png

Thank you very much for your help with this, I didn't know I could spend 8 hours trying to create a checkbox and still not have it work.

Dave
Book1.xlsm
ExcelScreenshot.png
Error-424.png

When I opened the workbook in Excel 2013, I found the following:
1. You were in fact using a Forms checkbox
2. The assigned macro in Module3 was incorrectly trying to refer to Checkbox1 as though that was a recognizable name. Had you used an ActiveX checkbox, it likely would have been named CheckBox1 and a click event macro in the Sheet1 code pane could have referred to CheckBox1 successfully. It errors, however, on a Forms checkbox named "Check Box 1".
3. In VBA, True converts to -1. So to test whether the Forms checkbox is checked, you compare the value to +1

Where there is one checkbox, there are likely to be more than one. Thinking that you might want to recycle the same code for multiple checkboxes, I used Application.Caller to return the name of the shape object that called the macro. This happens to be the name of the Forms checkbox, so for the particular problem I can use Application.Caller interchangeably with "Check Box 1" to refer to the name of the checkbox.

This code works:

Sub CheckBox1_Click()
  If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
    MsgBox Application.Caller & " is checked"
  End If
End Sub

Select allOpen in new window

Book1Q28126674.xlsm

Thanks again, Brad.  Your comment about me "trying to refer to Checkbox1 as though that was a recognizable name" made me realize I was confused about how to access forms controls programmatically, including confusing the Sub procedure name with the name of the control.

Once I sorted out my thinking, I was able to programmatically access the checkbox!

In doing this, I had one more question if you have the time.  When I create a checkbox manually, Excel seems to increment the name each time I create one, e.g. "Check Box 4", "Check Box 5", etc.   My observations are:

  1. Even if I delete a checkbox, Excel keeps numbering them sequentially.  For example,
      if I delete Check Box 4 and then create another one, it will be Check Box 6, if 6 is the
      next one in the sequence.

  2. Secondly, at least manually, it's not possible to rename a checkbox, even using the
      name box to the left of the formula bar.  For example, if I create Check Box 6, I
      can't manually change its name to "Compress Columns" or something like that.  At
      least manually.

Any thoughts you have on these final two questions would be appreciated.

Thanks again for all of your help!

Dave

Thanks Brad for your further follow-up, I went ahead and marked your last response as the solution to reflect your cumulative responses, all of which were great -- very detailed and helpful.  I really appreciate all of your assistance, easily an "Excellent" grade.

Dave