How to select 3 checkboxes out of 8 options.

R

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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
nemmi69 said:
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
Click to expand...


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
mikerickson said:
Are you positive that these are ActiveX checkboxes and not checkboxes from the Forms menu?
Click to expand...

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
MickG said:
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
Click to expand...


Dear MickG

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

Thanks everyone for your wonderful support.

Rajesh
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Working with Userform
Replies
1
Views
313
Photomofo
P
I
  • Question Question
Checkbox doesnt show checkmark when it should
Replies
2
Views
224
ipbr21054
I
P
  • Question Question
System error &H80004005 (-2147467259). Unspecified error
Replies
6
Views
466
jkpieterse
jkpieterse
I
  • Solved
Advice for my code that hides shapes on worksheet
Replies
3
Views
208
dmt32
D
B
  • Question Question
Make XMatch return "true" or "False"
Replies
5
Views
111
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,482
Messages
6,185,259
Members
453,283
Latest member
Shortm88

Share this page

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