Solved

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

Posted on 2013-05-13
9
14,916 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Outlook Free & Paid Tools
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…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

756 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