generic checkbox click form event handler?

[fedude]

I have a large form with 28 checkboxes on it. The checkboxes are named Qxxxx
where xxxx is a meaningful number that links me to the proper text box also
on the form. (textboxes are named Sxxxx). xxxx is a unique identifier and
not sequential.

I want to disable/enable the appropriate textbox if the corresponding
checkbox is clicked.

I'd prefer to not have to write 28 Qxxxx_Click routines with the exact same
code in all of them. Is there some way to create a generic checkbox_Click
event that can be handled by one routine? Or is there some way to write one
routine and have it handle all the checkbox_Click events?

 

[Joel]

Each check box must have a unique function. But all of the routines can have
one instruction which call a common function.

 

[fedude]

Joel,

Is there a generic form change event that I can catch in a routine that then
loops through the checkbox controls to see if they've changed?

 

[fedude]

Here is the routine I'm writing for each of the checkboxes. I'm unsure how
to genericize this so I can create control names from the name of the
checkbox.


Suggestions?
-----------------------------------------------

Private Sub Q1062_Click()
Dim player As Integer

'capture the unique number
player = Right(S1062.Name, 4)

S1062.Value = ""
If Q1062.Value = True Then
L1062.Tag = L1062.Caption
L1062.Caption = "Substitute"
S1062.Enabled = False
S1062.BackColor = &H8000000B
Else
L1062.Caption = L1062.Tag
S1062.Enabled = True
S1062.BackColor = &H80000005
End If
End Sub

 

[Joel]

These are the events


AddinInstall

AddinUninstall

BeforeClose

BeforePrint

BeforeSave

Deactivate

NewSheet

Open

PivotTableCloseConnection

PivotTableOpenConnection

SheetActivate

SheetBeforeDoubleClick

SheetBeforeRightClick

SheetCalculate

SheetChange

SheetDeactivate

SheetFollowHyperlink

SheetPivotTableUpdate

SheetSelectionChange

WindowActivate

WindowDeactivate

WindowResize


activeworkbook

 

[Dave Peterson]

John Walkenbach shows a way:
http://j-walk.com/ss/excel/tips/tip44.htm

His code actually uses commandbuttons, but it can be modified to use Checkboxes.

In the class module:
Option Explicit
Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Click()
MsgBox "Hello from " & CBXGroup.Name & vbLf & CBXGroup.Value
End Sub

In a General module:

Option Explicit
Dim myCBXes() As New Class1
Sub ShowDialog()
Dim CBXCount As Long
Dim ctl As Control

CBXCount = 0
For Each ctl In UserForm1.Controls
If TypeOf ctl Is MSForms.CheckBox Then
CBXCount = CBXCount + 1
ReDim Preserve myCBXes(1 To CBXCount)
Set myCBXes(CBXCount).CBXGroup = ctl
End If
Next ctl
UserForm1.Show
End Sub

Make sure you read John's instructions.

 

[Joel]

You need to use oleobjects like the code below. The oleobject doesn't have
the name of the box but it does have the caption. You need to do the same
thing with the other objects.


Private Sub Q1062_Click()
call common_click("1062")
end sub

common_click(box_num as string)

Dim player As Integer
set box = oleobject("Q" & box_num).object
'capture the unique number
player = Right(box.Name, 4)

box.Value = ""
If box.Value = True Then
L1062.Tag = L1062.Caption
L1062.Caption = "Substitute"
S1062.Enabled = False
S1062.BackColor = &H8000000B
Else
L1062.Caption = L1062.Tag
S1062.Enabled = True
S1062.BackColor = &H80000005
End If
End Sub

 

[Joel]

If you have a userform then add the group box around all the checkboxes. If
your form is a worksheet then add the group box from the toolbar "Forms" (add
from view menu if it is not one of your normal forms).

 

[fedude]

Dave,

Took a while for me to customize it for my checkboxes, but this works great.
Thanks!!!

 

[fedude]

Unfortunately, all the checkboxes are not located in a separate area of the
form. They are interspersed with text boxes.