• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15475
  • Last Modified:

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

Hi,

I have a very simple Excel VBA program to read the value of a Forms checkbox, which I created from the Developer > Insert menu.  Excel assigned the name CheckBox1 to my checkbox and created the "Sub" and "End Sub" statements for me.

I then inserted 3 lines to read the value of the checkbox and display a MsgBox if the checkbox is checked.  The complete program is:

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

VBA generates "Run-time error '424': Object required" on the 2nd line (the If statement).  This error was generated on my work computer so I came home and got the same error on my home computer.  I Googled for several hours and also looked here, but found nothing relevant other than lots of example programs which do the same thing I did above.

When I created the above program, I opened up a new spreadsheet and am on Sheet1 of Book1.  Checkbox1 is the only control I've generated.  Again, it's Forms, not ActiveX.

Thanks for your help with this.

Dave
0
Dave_Sweetser
Asked:
Dave_Sweetser
  • 5
  • 4
1 Solution
 
byundtCommented:
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.
0
 
byundtCommented:
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

Open 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

Open in new window

0
 
Dave_SweetserAuthor Commented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
byundtCommented:
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
0
 
Dave_SweetserAuthor Commented:
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
0
 
byundtCommented:
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

Open in new window

Book1Q28126674.xlsm
0
 
Dave_SweetserAuthor Commented:
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
0
 
byundtCommented:
Dave,
Your observation #1 really has to do with how Microsoft developers programmed Excel. They kept track of the most recently assigned name, and incremented from that without bothering to check if there were holes in the sequence due to deleted checkboxes.

Regarding your second point, however, I suspect that you weren't hitting the Enter key after changing the name.
1.  Right-click the checkbox to select it
2.  Click in the Address bar to the left of the Formula bar. You may need to do it more than once to get into Edit mode
3.  Type in a new name for the checkbox
4.  Hit Enter when done

It is interesting to note that after I changed the name of Check Box 1 to Check Box 13, the following statement in the Immediate pane returned "Check Box 13"
?ActiveSheet.Checkboxes("Check Box 1").Name

I then changed the name a second time, and the previous Immediate pane statement still worked (returning the new name). But this Immediate pane statement failed:
?ActiveSheet.Checkboxes("Check Box 13")

Brad
0
 
Dave_SweetserAuthor Commented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now