Capturing Checkbox Events

[Mark D'Agosta]

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.

 

[Bob Phillips]

Mark,

Not tried them myself in this particular instance, but here are 3 thoughts
or you to investigate

1) Add the checkbox click code to your workbook for all possible checkboxes
statically, and as each checkbox is added it will automatically pick up its
module, If they all do the same sort of thing, the actual doing code could
be put in a separate called module to reduce the amount of code. Obviously,
the problem here is that you need to know the limit.

2) Look at Chip Pearson's page on dynamically adding VBA code at
http://www.cpearson.com/excel/vbe.htm. This is complex, but means that you
should be able to add a checkbox click code procedure on the fly as well.

3) Check out John Walkenbach's technique for handling multiple controls at
http://j-walk.com/ss/excel/tips/tip44.htm. John's example uses command
buttons, but should be adaptable to checkboxes.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

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.)

 

[Tom Ogilvy]

Checkboxes.Add adds a checkbox from the forms toolbar. You can handle all
these events with one simple routine

Sub chbkx_click()
Dim cbx As CheckBox
Dim sName As String
Dim lnkCell As Range
sName = Application.Caller
Set cbx = ActiveSheet.CheckBoxes(sName)
Set lnkCell = Range(cbx.LinkedCell)
MsgBox cbx.Name & " was clicked, value in cell " & lnkCell.Address & _
" is " & lnkCell.Value

End Sub

When you add the checkbox just assign the onaction property to this sub

Here is an example:

Sub AddCheckBox()
Dim cbox As CheckBox
Set cbox = ActiveSheet.CheckBoxes.Add( _
ActiveCell.Left, ActiveCell.Top, _
ActiveCell.Width, ActiveCell.Height)
cbox.OnAction = "chbkx_click"
cbox.Value = xlOff
cbox.LinkedCell = ActiveCell.Offset(0, -1).Address
End Sub

 

[Mark D'Agosta]

Thanks Bob. Options 2 and 3 both look very interesting. I'll check them
out.

Mark D.

 

[Mark D'Agosta]

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.

 

[Mark D'Agosta]

Tom,

Thanks for your reply. Works great. However, as I also asked Dave
Peterson, I'd like to be able to add this OnAction function to the worksheet
module rather than a global module, but Excel 2000 won't let me do it. Is
there any way around this?

Thanks,
Mark

 

[Tom Ogilvy]

Inserting a second general module shouldn't be a big deal. I doubt it would
incur more overhead than putting code in a sheet module.

 

[Dave Peterson]

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.)