Checking if a worksheet-based checkbox is checked

Question

I'm trying to use an IF-clause to determine whether my checkbox, named "Check Box 1", is checked.

My current code:

Sub Button167_Click()
 If ActiveSheet.Shapes("Check Box 1") = True Then
 Range("Y12").Value = 1
 Else
 Range("Y12").Value = 0
 End If
End Sub

This doesn't work. The debugger is telling me there is a problem with the

ActiveSheet.Shapes("Check Box 1")

However, I know this code works (even though it serves a different purpose):

ActiveSheet.Shapes("Check Box 1").Select
With Selection
.Value = xlOn

My checkboxes (there are 200 on my page), are located in sheet1, by the name of "Demande". Each Checkbox is has the same formatted name of "Check Box ...".

@Motes Any way to quickly know, without me having to boot up Parallels? — Paolo Bernasconi, Jul 31 '12 at 14:26
Hi, can you please help me to get value of checkbox in javascript in same manner — Anuja P, Dec 03 '15 at 11:34
You can find another solution using `CodeName` [here](http://stackoverflow.com/a/36921631/1946796) — Rafa Barragan, Apr 28 '16 at 17:33

score 35 · Accepted Answer · answered Jul 31 '12 at 14:43

35

Sub Button167_Click()
 If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
 Range("Y12").Value = 1
 Else
 Range("Y12").Value = 0
 End If
End Sub

1 is checked, -4146 is unchecked, 2 is mixed (grey box)

answered Jul 31 '12 at 14:43

Motomotes

4,111
1
25
24

This worked like a charm. @Siddharth, your answer is correct as well – Paolo Bernasconi Jul 31 '12 at 14:47
how to get the value you in javascript code after parsing the excel file. I need the chekbox value from excel workbook http://stackoverflow.com/questions/34064853/parse-the-excel-workbook-and-get-the-checkbox-value-in-javascipt-or-angular – Anuja P Dec 03 '15 at 11:35
1

FYI -4146 == xlOff and 1 == xlOn – tomatoeshift May 26 '22 at 16:06

Siddharth Rout · Answer 2 · 2012-07-31T14:44:24.330

12

Is this what you are trying?

Sub Sample()
    Dim cb As Shape

    Set cb = ActiveSheet.Shapes("Check Box 1")

    If cb.OLEFormat.Object.Value = 1 Then
        MsgBox "Checkbox is Checked"
    Else
        MsgBox "Checkbox is not Checked"
    End If
End Sub

Replace Activesheet with the relevant sheetname. Also replace Check Box 1 with the relevant checkbox name.

edited Jul 31 '12 at 14:44

answered Jul 31 '12 at 14:33

Siddharth Rout

147,039
17
206
250

2

This will check the box, but to read it you need to set check the value against 1. – Motomotes Jul 31 '12 at 14:42
+1 I didn't know this before. I'm going to delete my reply as it may lead to confusion. Just tried it and indd it works. In this case checked seems to be verified by values 1 and 0 (not true and false). – html_programmer Jul 31 '12 at 14:43
@Motes: Yes. I have added that part. I was reading the post from blackberry so misread it. I thought the user wanted to check the checkbox. :P – Siddharth Rout Jul 31 '12 at 14:45
If you reused the shape reference, it might be more efficient, and he probably made it this way for readability. – Motomotes Jul 31 '12 at 14:53
@PaoloBernasconi: Could you please explain what do you mean by less efficient and how is it less efficient? – Siddharth Rout Jul 31 '12 at 14:53
@SiddharthRout, sorry less efficient is the wrong word to use. Motes's code is just shorter, even though I'm sure the executing time is the same for both of your answers. – Paolo Bernasconi Jul 31 '12 at 14:58
@PaoloBernasconi: Ok. Fair enough :) – Siddharth Rout Jul 31 '12 at 15:00

score 6 · Answer 3 · edited May 23 '23 at 03:24

6

In VBA macro code for an ActiveX checkbox control, you use

If ActiveSheet.OLEObjects("CheckBox1").Object.Value = True Then

and for a Form checkbox control, you use

If ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = xlOn Then

(Use xlOff or xlMixed to check for these respective states.)

edited May 23 '23 at 03:24

Rsge

53
10

answered Sep 18 '19 at 01:52

Allan F

2,110
1
24
29

score 4 · Answer 4 · edited May 23 '17 at 12:15

4

Building on the previous answers, you can leverage the fact that True is -1 and False is 0 and shorten your code like this:

Sub Button167_Click()
  Range("Y12").Value = _
    Abs(Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value > 0)
End Sub

If the checkbox is checked, .Value = 1.

Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value > 0 returns True.

Applying the Abs function converts True to 1.

If the checkbox is unchecked, .Value = -4146.

Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value > 0 returns False.

Applying the Abs function converts False to 0.

edited May 23 '17 at 12:15

Community

1
1

answered Jul 31 '12 at 20:27

JimmyPena

8,694
6
43
64

Hi, can you please help me to get value of checkbox in javascript in same manner – Anuja P Dec 03 '15 at 11:34

score -2 · Answer 5 · answered Jul 31 '12 at 14:08

-2

Try: Controls("Check Box 1") = True

answered Jul 31 '12 at 14:08

PowerUser

11,583
20
64
98

Checking if a worksheet-based checkbox is checked

5 Answers5

Linked

Related