Hi MOHAMMESALMAN,
See the attached sample file that creates UserForm CheckBoxes dynamically. It also uses a Class Event Handler that can process all CheckBox Events, instead of having to add one for each CheckBox in the UserForm module.
Instructions for creating a Class Event Handler:
a. To prevent a compile error, in the VBA Editor add the following library reference:
Tools -> References -> Microsoft Forms 2.0 Object Library (Excel 2003 version)
b. Create Class ClassCheckBox
The following line is required at the top of the Class:
Public WithEvents CheckBoxGroup As MSForms.CheckBox
There are several built in events that can be selected.
c. Put the following line at the top of an ordinary Code module:
Public myCheckBoxes() As New ClassCheckBox
d. Put the following calls in UserForm_Initialize:
(1) - Call CreateUserForm1DynamicCheckBoxes 'To CREATE Dynamic (created at runtime) controls
(2) - Call GenerateUserForm1CheckBoxControlArray 'To assign CheckBoxes to the CheckBoxGroup
Controls in the CheckBoxGroup will have events handled by ClassCheckBox
NOTE: If a UserForm Event exists, it will still be activated before the Class Event.
Class ClassCheckBox code. NOTE if the Class name is changed, other items in the code must be changed also.
Option Explicit
Public WithEvents CheckBoxGroup As MSForms.CheckBox
Private Sub CheckBoxGroup_Click()
'This is the Event Handler for CheckBox Click Events caused by CheckBoxes in the CheckBoxGroup
If Not bGblInhibitCheckBoxEvents Then
'Increment the CheckBox 'Click' Counter
iGblCheckBoxClickCount = iGblCheckBoxClickCount + 1
UserForm1.LabelStatus.Caption = _
"Count = " & iGblCheckBoxClickCount & _
" CheckBox Click Event from " & CheckBoxGroup.Name & " Value = " & CheckBoxGroup.Value
Call ProcessUserForm1CheckBoxgroupClickEvent(CheckBoxGroup.Name)
End If
End Sub
UserForm1 module code:
Option Explicit
Private Sub UserForm_Initialize()
'Initialize the CheckBox Click Counter
iGblCheckBoxClickCount = 0
'Create Dynamic (i.e. created at runtime) CheckBoxes
Call CreateUserForm1DynamicCheckBoxes
'Create the Control Array for CheckBoxGroup CheckBox Events
Call GenerateUserForm1CheckBoxControlArray
End Sub
In an ordinary code module such as Module1:
Option Explicit
Public myCheckBoxes() As New ClassCheckBox
Public bGblInhibitCheckBoxEvents As Boolean
Public iGblCheckBoxClickCount As Long
Sub DisplayUserForm1()
'UserForm1.Show vbModal 'This locks out Excel - probably best choice for PRODUCTION
UserForm1.Show vbModeless 'This allows access to the Spreadsheet - best choice for DEBUGGING
End Sub
Public Sub CreateUserForm1DynamicCheckBoxes()
'This generates the 'CheckBox' matrix for UserForm1
Const MAX_UserForm1_CHECKBOX_ROWS = 3
Const MAX_UserForm1_CHECKBOX_COLS = 2
Const MAX_UserForm1_CHECKBOX_CONTROLS = MAX_UserForm1_CHECKBOX_ROWS * MAX_UserForm1_CHECKBOX_COLS
Const nLeftOffset = 4
Const nTopOffset = 150
Const nCellHeight = 18
Const nCellWidth = 180
Const nRowSpace = 5
Const nColSpace = 10
Dim cCntrl As Control
Dim iCol As Integer
Dim iRow As Integer
Dim sGroupName As String
Dim sName As String
For iRow = 1 To MAX_UserForm1_CHECKBOX_ROWS
For iCol = 1 To MAX_UserForm1_CHECKBOX_COLS
''''''''''''''''''''''''''''''''''''''''''''
'Create CheckBox
''''''''''''''''''''''''''''''''''''''''''''
sName = "CheckBoxDynamic" & Format(iRow, "00") & Format(iCol, "00")
sGroupName = "GroupDynamic" & iCol
Set cCntrl = UserForm1.Controls.Add("Forms.CheckBox.1", sName, True)
With cCntrl
.Name = sName
.Caption = sName
.Width = nCellWidth
.Height = nCellHeight
.Top = nTopOffset + (nCellHeight + nRowSpace) * (iRow - 1)
.Left = nLeftOffset + (nCellWidth + nColSpace) * (iCol - 1)
.ZOrder (0) '0/1 Order on top/bottom
End With
Next iCol
Next iRow
End Sub
Sub GenerateUserForm1CheckBoxControlArray()
'This creates the array of CheckBoxes processed by the 'ClassCheckBox' Event Handler
Dim cCntrl As Control
Dim sName As String
Dim sTypeName As String
Dim iCheckBoxIndexMax As Integer
iCheckBoxIndexMax = 0
ReDim myCheckBoxes(1 To 1)
For Each cCntrl In UserForm1.Controls
sTypeName = TypeName(cCntrl)
If sTypeName = "CheckBox" Then
'Get the Control Name
sName = cCntrl.Name
'Debug.Print "CheckBox Name " & sName
iCheckBoxIndexMax = iCheckBoxIndexMax + 1
ReDim Preserve myCheckBoxes(1 To iCheckBoxIndexMax)
Set myCheckBoxes(iCheckBoxIndexMax).CheckBoxGroup = cCntrl
End If
Next cCntrl
End Sub
Sub ProcessUserForm1CheckBoxgroupClickEvent(sControlName As String)
'This Processes Click Events caused by CheckBoxes in the CheckBoxGroup
'Currently a Stub
End Sub
Lewis
Bookmarks