I've always put these type of routines in a General module. I don't quite see
the problem with putting it in a general module. You may be assigning the macro
to the checkbox, but the macro is being called by the click.

(I couldn't assign the macro to a sub in the userform in xl2002, either.)

Mark D'Agosta wrote:

Dave,

Thanks a lot for your reply. Your method works just fine. I'm an
experienced VB developer, but this is my first attempt at building an
application in Excel. Is there any way to place the OnAction code in the
form module rather than a general module. What's happening is that I'm
ending up with a bloated general module that has code being referenced from
all over the place. I guess I could create a general module for every form
and worksheet in the project (like back in the old VB3 days), but I'd rather
not.

I guess what I'm having the most trouble with is the barrier between the
AddIn's code and objects, and the AddIn's client worksheet code and objects.
It's sometimes difficult to determine what can be referenced from where, you
know?

Thanks again for your help,

Mark D.

"Dave Peterson" wrote in message
...
If you're using checkboxes.add, then you're using checkboxes from the
Forms
toolbar. (I find them simpler to use for things like this).

They have an .onaction property that can hold the name of the macro (in a
general module) that should be run when the checkbox is clicked.

Without knowing how/when you add the checkbox, I used this to add a
checkbox and
assign it a macro.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myCBX As CheckBox
Dim wks As Worksheet

Set wks = ActiveSheet

'someway you set the cell that gets the checkbox
With wks
Set myCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With myCell
.NumberFormat = ";;;"
.Locked = False
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
With myCBX
.Name = "cbx_" & myCell.Address(0, 0)
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Placement = xlMoveAndSize
.OnAction = ThisWorkbook.Name & "!CBXClick"
End With

End Sub

'this routine would be in a general module.
Sub CBXClick()
With ActiveSheet.CheckBoxes(Application.Caller)
If .Value = xlOn Then
MsgBox .Value & vbLf & .TopLeftCell.Address _
& vbLf & "it's checked"
Else
MsgBox .Value & vbLf & .TopLeftCell.Address _
& vbLf & "it's Not checked"
End If
End With
End Sub

If you use the checkboxes from the control toolbox toolbar, then the code
would
be under the sheet and the instructions from Bob would apply.

(But I think assigning an existing macro to a new checkbox (and having
that code
check to see where it got called) is a much simpler approach.)

Mark D'Agosta wrote:

I have code that dynamically creates a checkbox on each new line added
to a
worksheet (i.e. Activesheet.Checkboxes.Add statement is executed in the
Worksheet_Change event) and each newly created checkbox is linked to an
underlying cell. However, once these dynamically created checkboxes are
placed on the form, I don't seem to have any means of capturing clicks
on
them. Even though when I click the checkbox, the value in the
LinkedCell
changes accordingly, the Worksheet_Change event is not triggered. There
is
no Checkbox_Click for me to code to since they're created on the fly.
Anyone have any idea how I can capture the clicks on these checkboxes?

Thanks,

Mark D.

--

Dave Peterson


--

Dave Peterson