Solved

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

Posted on 2013-05-13
9
14,590 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
  • 5
  • 4
9 Comments
 
LVL 80

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 80

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
 
LVL 80

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 80

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 80

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now