View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
keepITcool keepITcool is offline
external usenet poster
 
Posts: 2,253
Default smart & tidy code for many checkBox_Change()

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