How to locate Cell in which control resides

[Jamal]

Hello,

I need help getting VBA to recognize the cell in which a checkbox
resides. Is there a way to know the cell position based on the
location of the control. The code I am using is:

With ActiveCell.Interior
If CheckBox1.Value = True Then
.Color = RGB(0, 255, 0)
Else
.Color = RGB(255, 0, 0)
End If
End With

I basically need to change the color of the cell based on whether the
control is checked or not.

Thanks very much in advance.

 

[Norman Jones]

Hi Jamal,

Try something like:

'=============>>
Private Sub CheckBox1_Click()
Dim Rng As Range

Set Rng = Me.CheckBox1.TopLeftCell

With Rng.Interior
If Me.CheckBox1.Value = True Then
.ColorIndex = 4
Else
.ColorIndex = 3
End If
End With
End Sub
'<<=============

 

[Tim Williams]

Forms checkbox or Control toolbox checkbox ?

If you use forms checkboxes you can assign the same macro to all and then
use Application.Caller within the macro to get a reference to the checkbox
itself

Sub CheckBoxes_Click()
MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address()
End Sub

Tim

 

[Tim]

To improve on my suggestion a little: if you name your checkboxes using the
cell address or row number which corresponds to their *intended* position
(since they occasionally aren't exactly where they were initially placed)
then you can extract that info from the name and use it directly.

Eg:

"0002_checkbox" for row 2

dim rownum as long
rownum=clng(left(application.caller,4))


Tim

 

[Jamal]

Sub CheckBoxes_Click()
MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address()
End Sub

Hi Tim,

This works great. I get a string like $H$8. This is what I was
looking for. I should be able to convert to a cell range and change
the color of that cell location. I am somewhat new to Excel
programming and do not know what the range format is. If I can
convert the string that Application.Caller provides, I feel like I can
complete this task. If you have the time to help with this, that would
be great... If not, no worries. I should be able to research and
make this work.

Thanks a million.

Jamal