# CheckBox cell in Excel Macro



## Student33 (May 12, 2011)

I'm trying to get the cell (row & column) where a checkbox is at a excel sheet, using VB code in an excel macro.
the idea is to modify the cell properties (color, fonto, other) when the checkbox is true, and that will be only for that particular cell (where the checkbox is placed).

Any ideas?

Thx.


----------



## Jimmy the Hand (Jul 28, 2006)

The checkbox is contained in a Shape object. The Shape object has a property called TopLeftCell, which returns a Range.
So you can query the checkbox coordinates this way:


```
Dim ws As Worksheet, s As Shape
    Set ws = ActiveSheet
    Set s = ws.Shapes("Checkbox1")
    MsgBox s.TopLeftCell.Row
    MsgBox s.TopLeftCell.Column
```
Jimmy


----------



## Student33 (May 12, 2011)

Thank you Jimmy. 
but now I have another question. How can I assign Row and Column value to a variable that I can use? for example, if I need to change the cell color to green or red (CheckBox true or false), I can not use the code below because an error msg come up saying that I need an object....
Set BoxRow = s.TopLeftCell.Row
Set BoxCol = s.TopLeftCell.Column
If CheckBox1.Value = True Then
ActiveSheet.Unprotect "Pass"
Sheets(ws).Cells(BoxRow, BoxCol).Interior.Color = RGB(146, 208, 80)
ActiveSheet.Protect "Pass"
Else
ActiveSheet.Unprotect "Pass"
Sheets(ws).Cells(BoxRow, BoxCol).Interior.Color = RGB(255, 0, 0)
ActiveSheet.Protect "Pass"
End If
' =This code was placed right below the one you provided=

Thx again for your answer and time.


----------



## Center (Jan 13, 2009)

Try running your code without "Set" for BoxRow and BoxCol. I think (in my VBA n00bness) the Set is only for objects. Looks like you're trying to assign a value which I don't use Set for.


----------



## Jimmy the Hand (Jul 28, 2006)

Center is right that Set is for objects only.
However, I don't think you need row and column values at all. Property TopLeftCell delivers you the cell itself, and you can paint it as you wish. For example:


```
Private Sub CheckBox1_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Unprotect "Pass"
    If CheckBox1.Value = True Then
        ws.Shapes("Checkbox1").TopLeftCell.Interior.Color = RGB(146, 208, 80)
    Else
        ws.Shapes("Checkbox1").TopLeftCell.Interior.Color = RGB(255, 0, 0)
    End If
    ws.Protect "Pass"
End Sub
```
Jimmy


----------



## Student33 (May 12, 2011)

Thx a lot Jimmy and Center! that works good.

Rgds,


----------

