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?

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

"fedude" wrote:

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,

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?

"Joel" wrote:

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

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

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

"fedude" wrote:

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?

"Joel" wrote:

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

Add a class module named Class1 (later name as say clsCBxEvnts)

' code in Class1
' add other events, select cbx from the middle dropdown
' then other events from the right dropdown

Public WithEvents cbx As MSForms.CheckBox
Public tbx As MSForms.TextBox

Private Sub cbx_Change()
tbx.Enabled = cbx.Value
End Sub

''Userform code

' two checkboxes named CheckBox1 & 2
' two textboxes named TextBox1 & 2

Dim arrClsCBoxEvnts(1 To 2) As Class1 ' or say clsCBxEvnts

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To 2
Set arrClsCBoxEvnts(i) = New Class1
With arrClsCBoxEvnts(i)
Set .cbx = Me.Controls("CheckBox" & i)
Set .tbx = Me.Controls("TextBox" & i)
.tbx.Enabled = .cbx.Value
End With
Next
End Sub

Regards,
Peter T

"fedude" wrote in message
...
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?

"Joel" wrote:

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

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.
fedude wrote:

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?

--

Dave Peterson

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


"fedude" wrote:

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

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).
"Dave Peterson" wrote:

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.
fedude wrote:

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?

--

Dave Peterson

Peter, you're my new hero. THANKS!!

Dave,

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

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