PDA

View Full Version : Solved: Limiting Toolbar Form Controls Checkboxes



JimS
11-16-2012, 12:29 PM
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



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

JimS
11-16-2012, 06:30 PM
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.




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

Aussiebear
11-17-2012, 01:30 AM
Jim, you might need to add a msgbox text to alert the user to the fact that only three checkboxes may be chosen.