View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 6,953
Default Help with macro that gets fired with check Box

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