Solved: Limiting Toolbar Form Controls Checkboxes

[JimS]

I have 6 Toolbar Form Controls Checkboxes (not Active-X style) and want to limit the maximum selected to 3.

I found some code online but cannot convert it to work with a Form Control Type Checkbox.

Any ideas?

Thanks...

JimS

[vba]

Sub ckb_1_Change()

Dim Count As Integer

Count = 0

If ckb_1.Value = True Then Count = Count + 1
If ckb_2.Value = True Then Count = Count + 1
If ckb_3.Value = True Then Count = Count + 1
If ckb_4.Value = True Then Count = Count + 1
If ckb_5.Value = True Then Count = Count + 1
If ckb_6.Value = True Then Count = Count + 1
If Count > 3 Then ckb_1.Value = False

End Sub

[/vba]

[JimS]

Ended up adding the following code for each of the Checkboxes (changing the "If Count > 3 Then ActiveSheet.Shapes("ckb_1").ControlFormat.Value = False" to match the individual Checkbox routine.


[vba]

Sub ckb_1_Change()
Dim Count As Integer
Count = 0
If ActiveSheet.Shapes("ckb_1").ControlFormat.Value = 1 Then Count = Count + 1
If ActiveSheet.Shapes("ckb_2").ControlFormat.Value = 1 Then Count = Count + 1
If ActiveSheet.Shapes("ckb_3").ControlFormat.Value = 1 Then Count = Count + 1
If ActiveSheet.Shapes("ckb_4").ControlFormat.Value = 1 Then Count = Count + 1
If ActiveSheet.Shapes("ckb_5").ControlFormat.Value = 1 Then Count = Count + 1
If ActiveSheet.Shapes("ckb_6").ControlFormat.Value = 1 Then Count = Count + 1
If Count > 3 Then ActiveSheet.Shapes("ckb_1").ControlFormat.Value = False
End Sub[/vba]

[Aussiebear]

Jim, you might need to add a msgbox text to alert the user to the fact that only three checkboxes may be chosen.