'Object Required' Error when trying to refer to CheckBox in worksheet

Question

I am trying to write a code in excel vba.

I want to change the color of the user selected cell when the checkbox is checked.

I have already written this code but it gives the 'object required' error on the line marked.

Sub CheckBox1_Click()    
Dim xRng As Range    
Set xRng = Selection

If CheckBox1.Value = True Then 'This is the error    
    xRng.Interior.Color = vbGreen    
End If

If CheckBox1.Value = False Then    
    xRng.Interior.Color = xlNone    
End If

End Sub

Please help me on how to debug this error. Thanks in advance! :)

Does [this thread](http://stackoverflow.com/a/11991419/4650297) help? Or perhaps [this one](https://www.mrexcel.com/forum/excel-questions/45786-if-clause-visual-basic-applications-look-checkbox-if-its-checked-not.html)? — BruceWayne, Mar 15 '17 at 13:44
Is your checkbox on a worksheet or a form? Is it an ActiveX control or a Form control if it's on a worksheet? — Jiminy Cricket, Mar 15 '17 at 13:46
@JiminyCricket A lot of people don't know the difference and thus are unaware which one they implemented. Hence, I always try to include the following link: http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20 — Ralph, Mar 15 '17 at 13:48
@JiminyCricket: My checkbox is on the worksheet. And it is Form Control. — Olivia, Mar 15 '17 at 13:48

Ralph · Accepted Answer · 2017-03-15T14:37:53.080

3

It seems to me that this is what you want:

Sub CheckBox1_Click()
Dim xRng As Range
Set xRng = Selection

If Worksheets("Sheet2").CheckBoxes("Check Box 1").Value = 1 Then
    xRng.Interior.Color = vbGreen
Else
    xRng.Interior.Color = xlNone
End If

End Sub

_{Don't forget to adjust the names Sheet2 and Check Box 1 to the actual names in your file.}

Here is a step-by-step video solution:

edited Mar 15 '17 at 14:37

answered Mar 15 '17 at 13:59

Ralph

9,284
4
32
42

This code gives me another error! `Sub CheckBox1_Click() Dim xRng As Range Set xRng = Selection If Worksheets("Sheet1").CheckBoxes("Checkbox1").Value = 1 Then 'Here is the error Allication-defined or object-defined error xRng.Interior.Color = vbGreen Else xRng.Interior.Color = xlNone End If End Sub` – Olivia Mar 15 '17 at 14:05
Sorry for the messy presentation. I just clicked enter so it got posted. I hope you can understand! – Olivia Mar 15 '17 at 14:09
Did you change `Sheet2` and `Check Box 1` according to the actual names you have in your Excel file? – Ralph Mar 15 '17 at 14:10
Yes I did change it the respective names I am using! – Olivia Mar 15 '17 at 14:16
1

Thank you so much! I am able to run the code now! I was using the edited text name as my checkbox name in the code and not its original name! – Olivia Mar 15 '17 at 14:52
1

@Pooja : In order : **Take the [tour] (click it)**, Accept and upvote this awesome answer and go back to your profile to do the same on helpful answers on your previous questions! ;) – R3uK Mar 15 '17 at 14:58

R3uK · Answer 2 · 2017-03-15T14:42:25.217

1

By default the code for such a control is placed in a regular module,
so you'll have to specify the sheet (change Sheet's name) on which your control is placed :

Sub CheckBox1_Click()    
Dim xRng As Range
Set xRng = Selection

If  ThisWorkBook.Sheets("Sheet's name").Shapes("Check Box 1").ControlFormat.Value = 1 Then
    xRng.Interior.Color = vbGreen
Else
    xRng.Interior.Color = xlNone
End If

End Sub

If you place that code in the sheet's module where the checkbox is,
the easiest way to fix this would be to try adding Me:

Sub CheckBox1_Click()    
Dim xRng As Range
Set xRng = Selection

If Me.Shapes("Check Box 1").ControlFormat.Value = 1 Then
    xRng.Interior.Color = vbGreen
Else
    xRng.Interior.Color = xlNone
End If

End Sub

edited Mar 15 '17 at 14:42

answered Mar 15 '17 at 13:51

R3uK

14,417
7
43
77

When I try to use this code, it gives me 'method or data member not found' error on the very first line! – Olivia Mar 15 '17 at 13:53
@Pooja : Do you mean `Sub CheckBox1_Click()` or `If Me.CheckBox1.Value `? – R3uK Mar 15 '17 at 13:55
I meant `Sub CheckBox1_Click()` This is where it gives the error 'method or data member not found' – Olivia Mar 15 '17 at 13:58
@Pooja : My bad, I thought that by default the code of the form would be place in the sheet's module, but it isn't! I'll edit my answer! ;) – R3uK Mar 15 '17 at 14:01
Now it gives me another error `Sub CheckBox1_Click() Dim xRng As Range Set xRng = Selection If Me.CheckBox1_Click.Value Then xRng.Interior.Color = vbGreen Else xRng.Interior.Color = xlNone End If End Sub` The error is 'Expected Function or Variable' for .CheckBox1_Click – Olivia Mar 15 '17 at 14:13
@Pooja : You used `Me.CheckBox1_Click.Value` **in your test** `If Me.CheckBox1_Click.Value Then`, you forgot to **get rid of the `_Click`** when copying! ;) Take a closer look at my answer, it should be `If Me.CheckBox1.Value Then` ;) – R3uK Mar 15 '17 at 14:18
Oh! I am so sorry! My bad. But, now I changed it and getting the error 'method or data member not found' on .CheckBox1 – Olivia Mar 15 '17 at 14:28
@Pooja : If you use `Me.CheckBox1.Value` is the code in the sheet's module? If not, try to move it there or try the 1st part of my answer. – R3uK Mar 15 '17 at 14:31
@Pooja : Try the edit! It's working smoothly for me! ;) – R3uK Mar 15 '17 at 14:42
Thank you so much for the help! Now I am able to run the macro using Ralph's code – Olivia Mar 15 '17 at 14:55
@Pooja : You are welcome, you can upvote as many posts as you want so don't hesitate! ;) But accept Ralph's one! ;) – R3uK Mar 15 '17 at 14:59

score 1 · Answer 3 · answered Mar 15 '17 at 14:06

1

I'd go like follows

Sub CheckBox1_Click()

    Selection.Interior.Color = IIf(ActiveSheet.CheckBoxes("CheckBox1").Value = xlOn, vbGreen, 16777215)

End Sub

answered Mar 15 '17 at 14:06

user3598756

28,893
4
18
28

@Pooja, did you try this solution? – user3598756 Mar 15 '17 at 14:18
I just tried this code. It gives me the error 'Application-defined or object-defined error' – Olivia Mar 15 '17 at 14:18
2

then either it's not a _Form_ checkbox (but rather an _ActiveX_ one), or it's not called "CheckBox1". Please check it out and let me know – user3598756 Mar 15 '17 at 14:19
I am sure about it being a form control. And, when I inserted the checkbox its name was 'Check Box 1'. I edited its name to 'CheckBox1' which we are using to write the code. – Olivia Mar 15 '17 at 14:25
what you depicted is precisely my testing enevironment. try selecting your checkbox (right click on it), type in Immediate Window `?typename(selection)`, press return and see the returned value – user3598756 Mar 15 '17 at 14:28
When I right click on the checkbox, I cannot find any immediate window where I can type in! The right click just opens up the cut, copy, paste, format control, etc. drop down! – Olivia Mar 15 '17 at 14:32
you must open your VBA IDE, and pop Immediate Window out by clicking CTRL+G.. And.... what's that "dropdown" about? – user3598756 Mar 15 '17 at 14:34
The returned value is CheckBox – Olivia Mar 15 '17 at 14:38
Thank you so much for the help! Now I am able to run the macro using Ralph's code – Olivia Mar 15 '17 at 14:54

'Object Required' Error when trying to refer to CheckBox in worksheet

3 Answers3

Linked