I only want one checkbox to be allowed at a time

  • Thread starter Thread starter Adeptus - ExcelForums.com
  • Start date Start date
A

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.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
Click to expand...
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!
 
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/
_______
 
Back
Top