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


--
nik_gujarathi
------------------------------------------------------------------------
nik_gujarathi's Profile: http://www.excelforum.com/member.php...o&userid=34522
View this thread: http://www.excelforum.com/showthread...hreadid=545191

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"


--
Regards,
Tom Ogilvy






"nik_gujarathi" wrote:


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


--
nik_gujarathi
------------------------------------------------------------------------
nik_gujarathi's Profile: http://www.excelforum.com/member.php...o&userid=34522
View this thread: http://www.excelforum.com/showthread...hreadid=545191

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



Tom Ogilvy Wrote:
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"


--
Regards,
Tom Ogilvy






"nik_gujarathi" wrote:


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


--
nik_gujarathi

------------------------------------------------------------------------
nik_gujarathi's Profile:
http://www.excelforum.com/member.php...o&userid=34522
View this thread:
http://www.excelforum.com/showthread...hreadid=545191




--
nik_gujarathi
------------------------------------------------------------------------
nik_gujarathi's Profile: http://www.excelforum.com/member.php...o&userid=34522
View this thread: http://www.excelforum.com/showthread...hreadid=545191

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.

--
Regards,
Tom Ogilvy

"nik_gujarathi" wrote:


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



Tom Ogilvy Wrote:
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"


--
Regards,
Tom Ogilvy






"nik_gujarathi" wrote:


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


--
nik_gujarathi

------------------------------------------------------------------------
nik_gujarathi's Profile:
http://www.excelforum.com/member.php...o&userid=34522
View this thread:
http://www.excelforum.com/showthread...hreadid=545191




--
nik_gujarathi
------------------------------------------------------------------------
nik_gujarathi's Profile: http://www.excelforum.com/member.php...o&userid=34522
View this thread: http://www.excelforum.com/showthread...hreadid=545191

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


Tom Ogilvy Wrote:
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.

--
Regards,
Tom Ogilvy

"nik_gujarathi" wrote:


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



Tom Ogilvy Wrote:
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"


--
Regards,
Tom Ogilvy






"nik_gujarathi" wrote:


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


--
nik_gujarathi


------------------------------------------------------------------------
nik_gujarathi's Profile:
http://www.excelforum.com/member.php...o&userid=34522
View this thread:
http://www.excelforum.com/showthread...hreadid=545191




--
nik_gujarathi

------------------------------------------------------------------------
nik_gujarathi's Profile:
http://www.excelforum.com/member.php...o&userid=34522
View this thread:
http://www.excelforum.com/showthread...hreadid=545191




--
nik_gujarathi
------------------------------------------------------------------------
nik_gujarathi's Profile: http://www.excelforum.com/member.php...o&userid=34522
View this thread: http://www.excelforum.com/showthread...hreadid=545191