Determine when checkbox is clicked

Hi,

I Have a bunch of checkboxes in a spreadhseet - what I wnat to do is run a
code when one of the checkboxes are clicked -- the code to be run needs to
adjust the vale in a cell adjacen to the check box. I could do this by
having different macros for each check box, however, what I'd like to do is
determine the row number to which the check box is attached, so I could loop
through the same macro for each box and place my code based on the row. I
can't use activecell.row because it could be different from the row of the
checked box.

any ideas?
wAyne_

Here are two subroutines. The first one adds a bunch of checkboxes from the
Forms toolbar to a range in the activesheet (b3:B10).

The second one adds one to the cell to the right (C3:C10) each time you check
the box.

The first one only needs to be run once--to set up the checkboxes on the
worksheet.

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
With myCBX.TopLeftCell.Offset(0, 1)
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
.Value = 1 'maybe???
End If
End With
End If

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wAyne wrote:
>
> Hi,
>
> I Have a bunch of checkboxes in a spreadhseet - what I wnat to do is run a
> code when one of the checkboxes are clicked -- the code to be run needs to
> adjust the vale in a cell adjacen to the check box. I could do this by
> having different macros for each check box, however, what I'd like to do is
> determine the row number to which the check box is attached, so I could loop
> through the same macro for each box and place my code based on the row. I
> can't use activecell.row because it could be different from the row of the
> checked box.
>
> any ideas?
> wAyne_

--

Dave Peterson