*Resolved* Checkbox VBA Syntax?

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

Code:

---

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

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.

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

VB Code:

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

:D

Still get the same error, even with your code. :confused:

http://homepages.nildram.co.uk/~proebuck/error.JPG

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

http://homepages.nildram.co.uk/~proebuck/error2.JPG

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

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

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. :confused:

Cheers
-Rob

Here you go.

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. :confused:

No prob.

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

Hello,

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

Quote:

---

Originally Posted by RobDog888

No prob.

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

---

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

I found, all is OK.
Thanks for the time :)

Attachment 182539

Quote:

---

Originally Posted by 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

---