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
'<<=============

 

[Jamal]

Norman,

Thanks very much for the help. The method you described works fine.
However, the spreadsheet I am trying to attach the code to has
hundreds of check boxes. It would great if there is a way to assign
the same macro to each of the checkboxes but still be able to learn
the location of the cell where the check box resides.

Is there a way to know the cell location without associating the
checkbox with a specific cell? Can Excel determine the location of
the box when it is checked or unchecked?

Your method works for one checkbox since the code associated with the
checkbox has to know what the name of the checkbox is:

Set Rng = Me.CheckBox1.TopLeftCell

What I would like to do is associate each checkbox with the same
macro. The macro would have the capability to find the cell location
of the check box as it is checked and unchecked. If the macro tells
what the cell location is, then I change its color.

Thanks in advance.

 

[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