Help with macro that gets fired with check Box

[nik_gujarathi]

Hello Friends
I am looking for a simple macro.
The value of cells in column M and J is linked to two checkboxs in each
row. When I check the first box, the value of cell in column M becomes
"TRUE", but when it is unchecked it becomes "False". Same is with the
second checkbox. The second check box is linked with cells in column J.

I want the user to check only one box in each row. I know this can be
done by using the Option Button & group box, but there are some other
macros in the same worksheet, which prohibits me from using the group
box. Hence I am using the check box option obtained from Form Toolbar.

I was looking for a macro which will get fired with any of the check
box & will give me an error message if the user checks both the boxes

 

[Guest]

Sub Box_Click()
dim cbox as Checkbox
Dim c as Checkbox
Dim rng as Range
set cbox = Activesheet.checkboxes(Application.Caller)
set rng = cbox.topLeftCell
if cbox.Value = xlOn then
for each c in Activesheet.Checkboxes
if c.Name <> cbox.name then
if c.topLeftCell.row = cbox.row then
if c.Value = cbox.Value then
msgbox "Both boxes checked"
exit sub
end if
end if
end if
Next
End if
End Sub

Assign this macro to all your checkboxes.

You could eliminate the looping by using a clever naming convention.
for examle a checkbox in column M row 10 would be named
cbox10M and in J cbox10J

then you could use something like:

With Activesheet
set cbox = .checkboxes(Application.Caller)
if right(cbox.name) = "J" then
set cbox1 = Checkboxes(Left(cbox.Name,len(cbox.name)-1) & "M")
else
set cbox1 = Checkboxes(Left(cbox.Name,len(cbox.name)-1) & "J")
end if
End with
if cbox1 = xlOn and cbox = xlOn then
msgbox "Problems"

 

[nik_gujarathi]

Tom,
I tried to use your code, but it gives me Run time Error '438':
Object doesn't support this property or method

When I try to debug, I get stuck on the thenth line i.e.
If c.TopLeftCell.Row = cbox.Row Then

What's Wrong with the code

 

[Guest]

An omission in my typing

that line should be:
If c.TopLeftCell.Row = cbox.TopLeftCell.Row Then

here is tested code that performed as expected

Sub Box_Click()
Dim cbox As CheckBox
Dim c As CheckBox
Dim rng As Range
Set cbox = ActiveSheet.CheckBoxes(Application.Caller)
Set rng = cbox.TopLeftCell
If cbox.Value = xlOn Then
For Each c In ActiveSheet.CheckBoxes
If c.Name <> cbox.Name Then
If c.TopLeftCell.Row = cbox.TopLeftCell.Row Then
If c.Value = cbox.Value Then
MsgBox "Both boxes checked"
Exit Sub
End If
End If
End If
Next
End If
End Sub

If you are sloppy in the placement of the checkboxes and the topleftcell of
the corresponding boxes is not in the same row, then this would cause a
problem as well.

 

[nik_gujarathi]

Tom,
Thank you
I appriciate your help. The code is working
Thankx again