Hi all,

I've got questions again I've got a userform with about 3
checkBoxes. If any of the checkBoxes is ticked or unticked, I'd lik
the same block of code to be executed. How can I write smart and tid
code instead of writing 30 checkBox1_Change() ? any idea? Thanks

All the best,
Fendi

--
Fendi
-----------------------------------------------------------------------
Fendic's Profile: http://www.excelforum.com/member.php...fo&userid=2395
View this thread: http://www.excelforum.com/showthread.php?threadid=39365

Hi Fendic,

that can be done by using a class module.


Insert a new CLASS MODULE. (not a normal module)
In the porperties window give it a unique name
(clsCombo in this example)

copy the code.


''' Code for ClassModule Named clsCombo
Option Explicit
'the trick is in the withevents keyword.
Public WithEvents m_cbo As MsForms.ComboBox

Private Sub m_cbo_Change()
MsgBox m_cbo.Value
End Sub


Insert a userform.
Add a few comboboxes
copy the code

''' Code for the userform
Option Explicit

'holds the (classed) objects
Dim colCombos As Collection

Private Sub UserForm_Initialize()
Dim ctl As MsForms.Control
Dim subCBO As clsCombo

Set colCombos = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
'dummy populate
ctl.List = Array(1 & ctl.Name, 2 & ctl.Name, 3 & ctl.Name)

'create(instantiate) a copy of your class
Set subCBO = New clsCombo
'assign the ctl to the class's combobox object
Set subCBO.m_cbo = ctl
'add to collection to keep the instance alive
colCombos.Add subCBO
End If
Next

End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Fendic wrote :


Hi all,

I've got questions again I've got a userform with about 30
checkBoxes. If any of the checkBoxes is ticked or unticked, I'd like
the same block of code to be executed. How can I write smart and tidy
code instead of writing 30 checkBox1_Change() ? any idea? Thanks

All the best,
Fendic

oops checkboxes not comboboex.. :)

Option Explicit

''' Code for ClassModule Named clsCheck
Public WithEvents m_chk As MsForms.CheckBox

Private Sub m_chk_Change()
Debug.Print m_chk.Name, m_chk.Value
End Sub


'holds the (classed) objects
Dim colChecks As Collection

Private Sub UserForm_Initialize()
Dim ctl As MsForms.Control
Dim subCHK As clsCheck

Set colChecks = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
'create(instantiate) a copy of your class
Set subCHK = New clsCheck
'assign the ctl to the class's Checkbox object
Set subCHK.m_chk = ctl
'add to collection to keep the instance alive
colChecks.Add subCHK
End If
Next

'initialize the state of the checkboxes
SetFlags 5

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox "The values of the checkboxes a " & GetFlags
End Sub

Sub SetFlags(Flags As Long)
Dim lng&, n&, chk As clsCheck
For Each chk In colChecks
chk.m_chk = CBool(Flags And 2 ^ n)
n = n + 1
Next
End Sub

Function GetFlags() As Long
Dim lng&, n&, chk As clsCheck
For Each chk In colChecks
If chk.m_chk Then
lng = lng + 2 ^ n
End If
n = n + 1
Next
GetFlags = lng
End Function



the last code is an (imo) efficient way to pass the
the values of a series of booleans to and from your form




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Fendic wrote :


Hi all,

I've got questions again I've got a userform with about 30
checkBoxes. If any of the checkBoxes is ticked or unticked, I'd like
the same block of code to be executed. How can I write smart and tidy
code instead of writing 30 checkBox1_Change() ? any idea? Thanks

All the best,
Fendic

Excellent, class module is exactly what i am looking for! thanks so muc
keepsITCool ;

--
Fendi
-----------------------------------------------------------------------
Fendic's Profile: http://www.excelforum.com/member.php...fo&userid=2395
View this thread: http://www.excelforum.com/showthread.php?threadid=39365