*Resolved* Checkbox VBA Syntax?

[TheRobster]

I've got a simple piece of code in Excel VBA which is:

Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    Range("A1").Value = 1
Else
    Range("A1").Value = 0
End If

End Sub

which is supposed to make 1 or 0 appear in cell A1 depending on whether or not a checkbox is ticked. However all I get is the error message 'Runtime error 424. Object required'.

I don't know what this means or why the code doesn't work and I can't find anything in Excel Help which explains what's wrong!

Anyone help?

Cheers
-Rob

[RobDog888]

The checkbox doesn't actually reside in the cell. it is contained in a
overlay on top of the cells. Refer to the object instead of the cell.

[TheRobster]

I think you misunderstood what it was that I'm trying to do. I know the checkbox doesn't actually reside in a cell, but what I want it to do is to be linked to cell so that when it is ticked/unticked (true/false) then this places the number 1 or 2 in cell A1.

This isn't what I want it to do in my spreadsheet exactly. What I want is for a range of cells to be coloured green or white, depending on the checkbox condition. So in English the code would read:

If CheckBox1 is ticked then
Cell A1 colour = green
If CheckBox1 is not ticked then
Cell A1 colour = white

[RobDog888]

Public Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        With Sheet1.Range("A1:A1").Cells(1, 1).Interior
            .ColorIndex = 10
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Else
        With Sheet1.Range("A1:A1").Cells(1, 1).Interior
            .ColorIndex = xlNone
        End With
    End If
End Sub

[TheRobster]

Still get the same error, even with your code.



It seems to be the second line it's having problems with.



Did it work on your PC? I don't understand why it doesn't do what I want it to do.

[RobDog888]

I have the code behind Sheet1. That may be the issue if you have
the code in the ThisWorkBook or a module. Reference the
workbook/worksheet before the checkbox.

HTH

[TheRobster]

I still can't get it to work.

Any chance you can provide a download link to your version of it? I must be doing something fundamentally wrong but I can't figure out what.

Cheers
-Rob

[RobDog888]

Here you go.

[TheRobster]

Cheers, that works how I imagined it.

Did you use an ActiveX control? I was using the Forms checkbox - this is the only difference as far as I can see between what I did and what you did, only yours works and mine doesn't.

[RobDog888]

No prob.

Yes, I added the checkbox from the toolbox toolbar and not
the checkbox from the forms collection.

[tadynis]

Hello,

how can I do it Office 2019 ?
I cant copy ant paste Your CheckBox
Where is toolbox toolbar in Office 2019 ?

No prob.

Yes, I added the checkbox from the toolbox toolbar and not
the checkbox from the forms collection.

[westconn1]

this is an old thread from 2004 and the people who were posting here may well no longer visit this forum, you should have started a new thread

i do not have office 2019, but i guess you should look in the developer tab >controls

[tadynis]

I found, all is OK.
Thanks for the time

this is an old thread from 2004 and the people who were posting here may well no longer visit this forum, you should have started a new thread

i do not have office 2019, but i guess you should look in the developer tab >controls