I only want one checkbox to be allowed at a time

[Adeptus - ExcelForums.com]

Here is a new code I have written.

What I'd like is to know if there's some code that I can put in to
make all checkboxes = false, or ensure that you can't have multiple
checkboxes checked.



Private Sub CommandButton66_Click()

Dim LastRow As Object

If CheckBox1 = "True" Then

Sheets("U14 Single").Select
Else
GoTo 2
End If

2
If CheckBox2 = "True" Then

Sheets("U14 Large").Select
Else
GoTo 3
End If

3
If CheckBox3 = "True" Then

Sheets("14-18 Single").Select
Else
GoTo 4
End If

4
If CheckBox4 = "True" Then

Sheets("14-18 Large").Select
Else
GoTo 5
End If

5
If CheckBox5 = "True" Then

Sheets("Open Single").Select
Else
GoTo 6
End If

6
If CheckBox6 = "True" Then

Sheets("Open Large").Select

End If


Set LastRow = Range("A400").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text

MsgBox "Entry successfully written to Data Table"

response = MsgBox("Do you want to print the Entry
Certificate now?", vbYesNo)

If response = vbYes Then
Range("A" & Range("E3"),
"C" & Range("E3")).Select
Selection.Copy
Sheets("Printout").Select
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("U14 Single").Select
Range("A5").Select

MsgBox "Entry successfully printed!"

End If

response = MsgBox("Do you want to input another
Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

 

[Jon Peltier]

Use option buttons.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[anne.troy]

Forget all the code, and just put the checkboxes in a group (that's why
they call them option buttons and that's what they're really for...).
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Adeptus - ExcelForums.com]

Following is the code that I wrote following the last post I made. I
may well be convoluted, it might have redundant bits, but it work
smoothly as I could have asked for

So what do the things you recommended mean? How do you go about doin
that... y'know I've only been tinkering for a couple of weeks so I a
no good with terms and jargon

Private Sub CheckBox1_Click(
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox2_Click(
CheckBox1 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox3_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox4 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox4_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox5_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox6_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox5 = Locke

End Su

Private Sub CommandButton66_Click(

Dim LastRow As Objec

If CheckBox1 = "True" The
Sheets("U14 Single").Selec
Els
GoTo
End I


If CheckBox2 = "True" The
Sheets("U14 Large").Selec
Els
GoTo
End I


If CheckBox3 = "True" The
Sheets("14-18 Single").Selec
Els
GoTo
End I


If CheckBox4 = "True" The
Sheets("14-18 Large").Selec
Els
GoTo
End I


If CheckBox5 = "True" The
Sheets("Open Single").Selec
Els
GoTo
End I


If CheckBox6 = "True" The
Sheets("Open Large").Selec
End I


Set LastRow = Range("A400").End(xlUp

LastRow.Offset(1, 0).Value = TextBox1.Tex
LastRow.Offset(1, 1).Value = TextBox2.Tex
LastRow.Offset(1, 2).Value = TextBox3.Tex

MsgBox "Entry successfully written to Data Table

response = MsgBox("Do you want to print the Entr
Certificate now?", vbYesNo

If response = vbYes The
Range("A" & Range("E3")
"C" & Range("E3")).Selec
Selection.Cop
Sheets("Printout").Selec
Range("M12").Selec
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Operation:=
xlNone, SkipBlanks:=False, Transpose:=Tru
Range("A1").Selec
Application.CutCopyMode = Fals
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Collate:=Tru
Sheets("U14 Single").Selec
Range("A5").Selec

MsgBox "Entry successfully printed!

End I

response = MsgBox("Do you want to input anothe
Entry?",
vbYesNo

If response = vbYes The
TextBox1.Text = "
TextBox2.Text = "
TextBox3.Text = "
CheckBox1 = Fals
CheckBox2 = Fals
CheckBox3 = Fals
CheckBox4 = Fals
CheckBox5 = Fals
CheckBox6 = Fals

TextBox1.SetFocu

Els
Unload M
End I

End Su

 

[Jon Peltier]

Option buttons within a group can only be selected one at a time.
Checkboxes have no such restriction, so you need lots of coding.

Replace your checkboxes with option butotns.

What does this mean:

CheckBox2 = Locked

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Adeptus - ExcelForums.com]

Option buttons within a group can only be selected one at a time.

Checkboxes have no such restriction, so you need lots of coding.

Replace your checkboxes with option butotns.

What does this mean:

Quote:
CheckBox2 = Locked


- Jon

that line, along with all the other CheckBoxClick lines, have the
effect of making it so you can only tick one box at a time, whichever
box is ticked, all the rest of them become locked and unticked. I'm
not 100% on how it works, but it really does work very well!

 

[Jon Peltier]

Why do you keep saying "locked"? Click one option button, it becomes
true and the other false, but there's no prohibition on clicking any
other option button.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______