Userform CheckBox question.

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Hi all,

I looked through the forums and didn't seem to fin anything like what I am trying to do.

I have 21 CheckBoxes. Named CheckBox1 - CheckBox21..

I need a macro the will do the following:

When any of the CheckBoxes within CheckBox1 - CheckBox21 is clicked / checked then all other checkboxes are greyed out / disabled but the one that was checked?

The what code can I use on my commandbutton to enable them all back at a later time?

Thanks.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
When do you want this to occur?

Whenever a checkbox is clicked?

If you do then what you probably need is a class module.

I'll try and rustle something up and post back.

By the way why not just use option buttons?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Right this is what I came up with.

When you click a checkbox then it disables all the rest.

When you click it again it re-enables all the checkboxes.

In a Class module called CCheckBoxes put this code.
Code:
Option Explicit

Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Click()
Dim ctl As MSForms.Control

    For Each ctl In UserForm1.Controls
        If CheckGroup.Value Then
            ctl.Enabled = ctl.Caption = CheckGroup.Caption
        Else
            ctl.Enabled = True
        End If
    Next ctl
End Sub
In the userform module put this code.
Code:
Option Explicit

Dim CheckBoxes() As New CCheckBoxes

Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            I = I + 1
            ReDim Preserve CheckBoxes(1 To I)
            Set CheckBoxes(I).CheckGroup = ctl
        End If
    Next
End Sub
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
What is the difference, other than apperence, between the optionbox and the check box?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
You can only pick one option button out of a group.

To group option buttons together use the GroupName property, which can easily be set by selecting all the option buttons for the group.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,116
Messages
5,570,273
Members
412,316
Latest member
JabirS
Top