Solved

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

Posted on 2013-05-13
9
14,972 Views
Last Modified: 2013-05-17
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
Comment
Question by:Dave_Sweetser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39163038
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
 
LVL 81

Expert Comment

by:byundt
ID: 39163062
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
 

Author Comment

by:Dave_Sweetser
ID: 39163184
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
Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

 
LVL 81

Expert Comment

by:byundt
ID: 39163279
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
 

Author Comment

by:Dave_Sweetser
ID: 39163338
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
 
LVL 81

Expert Comment

by:byundt
ID: 39163355
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
 

Author Comment

by:Dave_Sweetser
ID: 39164672
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
 
LVL 81

Accepted Solution

by:
byundt earned 240 total points
ID: 39164740
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
 

Author Closing Comment

by:Dave_Sweetser
ID: 39175837
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question