How to lock multiple checkboxes

[anthonymedz]

Hi,

I would like to ask how to lock multiple checkboxes in VBA instead of manually locking the checkboxes one by one?

Private Sub CheckBox1_Click()
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select

'CheckBox1.Locked = CheckBox1.Value
CheckBox2.Locked = CheckBox1.Value
CheckBox3.Locked = CheckBox1.Value
CheckBox4.Locked = CheckBox1.Value
CheckBox5.Locked = CheckBox1.Value
.
.
.
.
CheckBox999.Locked = CheckBox1.Value

 

[eiloken]

Hi,

I would like to ask how to lock multiple checkboxes in VBA instead of manually locking the checkboxes one by one?

Private Sub CheckBox1_Click()
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select

'CheckBox1.Locked = CheckBox1.Value
CheckBox2.Locked = CheckBox1.Value
CheckBox3.Locked = CheckBox1.Value
CheckBox4.Locked = CheckBox1.Value
CheckBox5.Locked = CheckBox1.Value
.
.
.
.
CheckBox999.Locked = CheckBox1.Value

those check box in userform or sheet?

 

[anthonymedz]

those check box in userform or sheet?

in userform

 

[eiloken]

first you need create new "Class Module" with name "Class1" then place this code into it:

Option Explicit

Public WithEvents newChB As MSForms.CheckBox

Private Sub newChB_Change()
    Dim cb As Control
    For Each cb In UserForm1.Controls
        If TypeOf cb Is MSForms.CheckBox Then
            If Not cb Is newChB Then cb.Locked = newChB.Value
        End If
    Next cb
End Sub

next, create new module and place this code at first:

Option Explicit

Private newCheckBox() As New Class1

Sub CheckBoxControl()
    Dim cb As Control
    Dim i As Integer
    ReDim newCheckBox(1000)
    For Each cb In UserForm1.Controls 'change UserForm1 to your userform name
        If TypeOf cb Is MSForms.CheckBox Then
            Set newCheckBox(i).newChB = cb
            i = i + 1
        End If
    Next cb
End Sub

and final is call CheckBoxControl in Initialize Event of your userform:

Private Sub UserForm_Initialize()
    Call CheckBoxControl
End Sub

 

[eiloken]

first you need create new "Class Module" with name "Class1" then place this code into it:

Option Explicit

Public WithEvents newChB As MSForms.CheckBox

Private Sub newChB_Change()
    Dim cb As Control
    For Each cb In UserForm1.Controls
        If TypeOf cb Is MSForms.CheckBox Then
            If Not cb Is newChB Then cb.Locked = newChB.Value
        End If
    Next cb
End Sub

next, create new module and place this code at first:

Option Explicit

Private newCheckBox() As New Class1

Sub CheckBoxControl()
    Dim cb As Control
    Dim i As Integer
    ReDim newCheckBox(1000)
    For Each cb In UserForm1.Controls 'change UserForm1 to your userform name
        If TypeOf cb Is MSForms.CheckBox Then
            Set newCheckBox(i).newChB = cb
            i = i + 1
        End If
    Next cb
End Sub

and final is call CheckBoxControl in Initialize Event of your userform:

Private Sub UserForm_Initialize()
    Call CheckBoxControl
End Sub

with this code, you can lock and unlock all checkboxes except that you clicked

 

[anthonymedz]

with this code, you can lock and unlock all checkboxes except that you clicked

Thank you for this... What if i have several/many userforms?


Option Explicit

Private newCheckBox() As New Class1

Sub CheckBoxControl()
Dim cb As Control
Dim i As Integer
ReDim newCheckBox(1000)
For Each cb In UserForm1.Controls 'change UserForm1 to your userform name
If TypeOf cb Is MSForms.CheckBox Then
Set newCheckBox(i).newChB = cb
i = i + 1
End If
Next cb
End Sub

 

[eiloken]

then

Thank you for this... What if i have several/many userforms?


Option Explicit

Private newCheckBox() As New Class1

Sub CheckBoxControl()
Dim cb As Control
Dim i As Integer
ReDim newCheckBox(1000)
For Each cb In UserForm1.Controls 'change UserForm1 to your userform name
If TypeOf cb Is MSForms.CheckBox Then
Set newCheckBox(i).newChB = cb
i = i + 1
End If
Next cb
End Sub

then change it like this:

Option Explicit

Private newCheckBox() As New Class1

Sub CheckBoxControl(ByVal uf As UserForm)
    Dim cb As Control
    Dim i As Integer
    ReDim newCheckBox(1000)
    For Each cb In uf.Controls
        If TypeOf cb Is MSForms.CheckBox Then
            Set newCheckBox(i).newChB = cb
            i = i + 1
        End If
    Next cb
End Sub

and

Private Sub UserForm_Initialize()
    Call CheckBoxControl(Me)
End Sub

to do it for each userform, you should call "CheckBoxControl" for all Initialize event of each userform

 

[anthonymedz]

then

then change it like this:

Option Explicit

Private newCheckBox() As New Class1

Sub CheckBoxControl(ByVal uf As UserForm)
    Dim cb As Control
    Dim i As Integer
    ReDim newCheckBox(1000)
    For Each cb In uf.Controls
        If TypeOf cb Is MSForms.CheckBox Then
            Set newCheckBox(i).newChB = cb
            i = i + 1
        End If
    Next cb
End Sub

and

Private Sub UserForm_Initialize()
    Call CheckBoxControl(Me)
End Sub

to do it for each userform, you should call "CheckBoxControl" for all Initialize event of each userform

What will be my program for Class module?

 

[eiloken]

What will be my program for Class module?

still the same, if all your user forms in same file then just need call CheckBoxControl for each user form, only need 1 class module

 

[anthonymedz]

still the same, if all your user forms in same file then just need call CheckBoxControl for each user form, only need 1 class module

This will be my 1 class module?

Option Explicit

Public WithEvents newChB As MSForms.CheckBox

Private Sub newChB_Change()
Dim cb As Control
For Each cb In UserForm1.Controls
If TypeOf cb Is MSForms.CheckBox Then
If Not cb Is newChB Then cb.Locked = newChB.Value
End If
Next cb
End Sub