How to select 3 checkboxes out of 8 options.

Rajesharihant

New Member
Joined
Jul 19, 2018
Messages
5
Dear All,


I am trying since morning to work on this, I am creating an excel file which has 8 selections & i want users to select any 3. after 3 selections the rest of the selections shall disable. similarly, if user deselect any of the checked 3 options, the remaining options shall get enabled.
I am using activeX checkboxes and a VBA code int he excel - macro enabled worksheet.
My Idea is to just count the enabled checkboxes, and then compare the count (if higher then 3) to enable disable all checkboxes.
the issue i am facing, i guess is the checkboxes when i do a sum, it only works for 0 & 1. it doesn't go 2,3,4...
attached enter image description here is the code I have written



Code:
Private Sub CheckBox1_Click()
Dim Raj1 As Integer
Dim Raj2 As Integer
Dim Raj3 As Integer
Dim Raj4 As Integer
Dim Raj5 As Integer
Dim Raj6 As Integer
Dim Raj7 As Integer
Dim Raj8 As Integer
Dim Total As Long


If CheckBox9.Enabled = True Then


Raj1 = CInt(CheckBox1.Value)
Raj2 = CInt(CheckBox2.Value)
Raj3 = CInt(CheckBox3.Value)
Raj4 = CInt(CheckBox4.Value)
Raj5 = CInt(CheckBox5.Value)
Raj6 = CInt(CheckBox6.Value)
Raj7 = CInt(CheckBox7.Value)
Raj8 = CInt(CheckBox8.Value)
Total = Raj1 + Raj2 + Raj3


End If


If Total > 2 Then
    CheckBox6.Enabled = True
    CheckBox7.Enabled = True
    CheckBox8.Enabled = True
    Else
    CheckBox6.Enabled = False
    CheckBox7.Enabled = False
    CheckBox8.Enabled = False
    End If
End Sub


Private Sub CheckBox2_Click()
Dim Raj1 As Integer
Dim Raj2 As Integer
Dim Raj3 As Integer
Dim Raj4 As Integer
Dim Raj5 As Integer
Dim Raj6 As Integer
Dim Raj7 As Integer
Dim Raj8 As Integer
Dim Total As Long


If CheckBox9.Enabled = True Then


Raj1 = CInt(CheckBox1.Value)
Raj2 = CInt(CheckBox2.Value)
Raj3 = CInt(CheckBox3.Value)
Raj4 = CInt(CheckBox4.Value)
Raj5 = CInt(CheckBox5.Value)
Raj6 = CInt(CheckBox6.Value)
Raj7 = CInt(CheckBox7.Value)
Raj8 = CInt(CheckBox8.Value)
Total = Raj1 + Raj2 + Raj3
End If




If Total = 1 Then
    CheckBox6.Enabled = True
    CheckBox7.Enabled = True
    CheckBox8.Enabled = True
    Else
    CheckBox6.Enabled = False
    CheckBox7.Enabled = False
    CheckBox8.Enabled = False
    End If
End Sub


Private Sub CheckBox3_Click()
Dim Raj1 As Integer
Dim Raj2 As Integer
Dim Raj3 As Integer
Dim Raj4 As Integer
Dim Raj5 As Integer
Dim Raj6 As Integer
Dim Raj7 As Integer
Dim Raj8 As Integer
Dim Total As Long


If CheckBox9.Enabled = True Then


Raj1 = CInt(CheckBox1.Value)
Raj2 = CInt(CheckBox2.Value)
Raj3 = CInt(CheckBox3.Value)
Raj4 = CInt(CheckBox4.Value)
Raj5 = CInt(CheckBox5.Value)
Raj6 = CInt(CheckBox6.Value)
Raj7 = CInt(CheckBox7.Value)
Raj8 = CInt(CheckBox8.Value)
Total = Raj1 + Raj2 + Raj3
End If




If Total = 1 Then
    CheckBox6.Enabled = True
    CheckBox7.Enabled = True
    CheckBox8.Enabled = True
    Else
    CheckBox6.Enabled = False
    CheckBox7.Enabled = False
    CheckBox8.Enabled = False
    End If
End Sub
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try

In Main module
Code:
Public SumCheckBoxes As Integer

Sub CheckEnabledBoxs()

SumCheckBoxes = 0
If Worksheets("Sheet1").CheckBox1.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox2.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox3.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox4.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox5.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox6.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox7.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox8.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox9.Value = True Then SumCheckBoxes = SumCheckBoxes + 1


End Sub


In sheet with checkboxes
Code:
Private Sub CheckBox1_Click()
CheckEnabledBoxs
If SumCheckBoxes >= 3 Then
    Worksheets("Sheet1").CheckBox1.Value = False
Else
    Worksheets("Sheet1").CheckBox1.Value = True
End If
End Sub



Repeat for each checkbox
 
Last edited:
Upvote 0
If this were on a userform, I'd use code like this. The syntax should be very similar for ActiveX checkboxes on a worksheet.
Code:
Private Sub CheckBox1_Click()
    checkOver
End Sub
Private Sub CheckBox2_Click()
    checkOver
End Sub
Private Sub CheckBox3_Click()
    checkOver
End Sub
Private Sub CheckBox4_Click()
    checkOver
End Sub
Private Sub CheckBox5_Click()
    checkOver
End Sub
Private Sub CheckBox6_Click()
    checkOver
End Sub
Private Sub CheckBox7_Click()
    checkOver
End Sub
Private Sub CheckBox8_Click()
    checkOver
End Sub

Sub checkOver()
    Dim CheckedCount As Long, ManyChecked As Boolean
    Dim i As Long
    
    CheckedCount = CheckedCount + CLng(CheckBox1.Value)
    CheckedCount = CheckedCount + CLng(CheckBox2.Value)
    CheckedCount = CheckedCount + CLng(CheckBox3.Value)
    CheckedCount = CheckedCount + CLng(CheckBox4.Value)
    CheckedCount = CheckedCount + CLng(CheckBox5.Value)
    CheckedCount = CheckedCount + CLng(CheckBox6.Value)
    CheckedCount = CheckedCount + CLng(CheckBox7.Value)
    CheckedCount = CheckedCount + CLng(CheckBox8.Value)
    CheckedCount = Abs(CheckedCount)
    
    ManyChecked = (2 < CheckedCount)
    
    For i = 1 To 8
        With Me.Controls("CheckBox" & i)
            .Enabled = (ManyChecked Imp .Value)
        End With
    Next i
End Sub
 
Upvote 0
Try this:-
Code:
Private [COLOR=navy]Sub[/COLOR] CheckBox1_Click()
 TB CheckBox1
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox2_Click()
TB CheckBox2
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox3_Click()
TB CheckBox3
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox4_Click()
TB CheckBox4
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox5_Click()
TB CheckBox5
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox6_Click()
TB CheckBox6
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox7_Click()
TB CheckBox7
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox8_Click()
TB CheckBox8
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]

Sub[/COLOR] TB(Ob [COLOR=navy]As[/COLOR] MSForms.CheckBox)
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]If[/COLOR] Ob.Value = False [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 1 To 8
        [COLOR=navy]With[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object
            .Enabled = True
            .Value = False
        [COLOR=navy]End[/COLOR] With
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]ElseIf[/COLOR] Ob.Value = True [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 1 To 8
        [COLOR=navy]If[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object = True [COLOR=navy]Then[/COLOR]
            c = c + 1
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]Next[/COLOR] n
[COLOR=navy]If[/COLOR] c = 3 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 1 To 8
        [COLOR=navy]If[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object.Value = False [COLOR=navy]Then[/COLOR]
            ActiveSheet.OLEObjects("CheckBox" & n).Object.Enabled = False
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Try

In Main module
Code:
Public SumCheckBoxes As Integer

Sub CheckEnabledBoxs()

SumCheckBoxes = 0
If Worksheets("Sheet1").CheckBox1.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox2.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox3.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox4.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox5.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox6.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox7.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox8.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox9.Value = True Then SumCheckBoxes = SumCheckBoxes + 1


End Sub


In sheet with checkboxes
Code:
Private Sub CheckBox1_Click()
CheckEnabledBoxs
If SumCheckBoxes >= 3 Then
    Worksheets("Sheet1").CheckBox1.Value = False
Else
    Worksheets("Sheet1").CheckBox1.Value = True
End If
End Sub



Repeat for each checkbox


Thanks nemmi69

I can understand what you are suggesting.
I did the same as per your suggestion - but during the execution of the

Sub CheckEnabledBoxs()

SumCheckBoxes = 0
If Worksheets("Sheet1").CheckBox1.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox2.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox3.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox4.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox5.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox6.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox7.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox8.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").CheckBox9.Value = True Then SumCheckBoxes = SumCheckBoxes + 1

while executing the last code and while returning the value of sumcheckboxes
Runtime error 438 appears saying Object doesnt support this property of method....

any suggestion on this?
or this is due to checkbox property/limitation.

Rajesh
 
Upvote 0
Are you positive that these are ActiveX checkboxes and not checkboxes from the Forms menu?

my script is like this now :

Private Sub CheckBox1_Click()
CheckEnabledBoxs
If SumCheckBoxes >= 3 Then
Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False
Else
Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True
End If
End Sub


Private Sub CheckBox2_Click()
CheckEnabledBoxs
If SumCheckBoxes >= 3 Then
Worksheets("Sheet1").OLEObjects("CheckBox2").Object.Value = False
Else
Worksheets("Sheet1").OLEObjects("CheckBox2").Object.Value = True
End If
End Sub


Private Sub CheckBox3_Click()
CheckEnabledBoxs
If SumCheckBoxes >= 3 Then
Worksheets("Sheet1").OLEObjects("CheckBox3").Object.Value = False
Else
Worksheets("Sheet1").OLEObjects("CheckBox3").Object.Value = True
End If
End Sub

***for 8 checkboxes


and the Main Module script is like this

Public SumCheckBoxes As Integer


Sub CheckEnabledBoxs()


Dim SumCheckBoxes As Integer


SumCheckBoxes = 0
If Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox2").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox3").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox4").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox5").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox6").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox7").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1
If Worksheets("Sheet1").OLEObjects("CheckBox8").Object.Value = True Then SumCheckBoxes = SumCheckBoxes + 1






End Sub

I am not getting the results yet, just trying to understand whats happening with this script...
atlest all the script is executing in debug mode...
 
Upvote 0
Try this:-
Code:
Private [COLOR=navy]Sub[/COLOR] CheckBox1_Click()
 TB CheckBox1
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox2_Click()
TB CheckBox2
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox3_Click()
TB CheckBox3
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox4_Click()
TB CheckBox4
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox5_Click()
TB CheckBox5
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox6_Click()
TB CheckBox6
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox7_Click()
TB CheckBox7
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] CheckBox8_Click()
TB CheckBox8
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]

Sub[/COLOR] TB(Ob [COLOR=navy]As[/COLOR] MSForms.CheckBox)
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]If[/COLOR] Ob.Value = False [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 1 To 8
        [COLOR=navy]With[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object
            .Enabled = True
            .Value = False
        [COLOR=navy]End[/COLOR] With
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]ElseIf[/COLOR] Ob.Value = True [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 1 To 8
        [COLOR=navy]If[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object = True [COLOR=navy]Then[/COLOR]
            c = c + 1
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]Next[/COLOR] n
[COLOR=navy]If[/COLOR] c = 3 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 1 To 8
        [COLOR=navy]If[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object.Value = False [COLOR=navy]Then[/COLOR]
            ActiveSheet.OLEObjects("CheckBox" & n).Object.Enabled = False
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick


Dear MickG

Thanks a lot for your support.
your code worked like a charm.

Thanks everyone for your wonderful support.

Rajesh
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top