I only want one checkbox to be allowed at a time

Adeptus - ExcelForums.com · 07-08-2005, 12:05 AM

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

[email protected] · 07-08-2005, 12:05 AM

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

Jon Peltier · 07-08-2005, 12:05 AM

Use option buttons.

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

Adeptus - ExcelForums.com wrote:

> 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
>
>
>

Adeptus - ExcelForums.com · 07-08-2005, 09:05 AM

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

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

Private Sub CheckBox1_Click()
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox2_Click()
CheckBox1 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox3_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox4 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox4_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox5_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox6_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox5 = Locked

End Sub

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 = ""
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox5 = False
CheckBox6 = False

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Jon Peltier · 07-08-2005, 09:05 PM

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 wrote:

> Following is the code that I wrote following the last post I made. It
> may well be convoluted, it might have redundant bits, but it works
> smoothly as I could have asked for.
>
> So what do the things you recommended mean? How do you go about doing
> that... y'know I've only been tinkering for a couple of weeks so I am
> no good with terms and jargon.
>
>
> Private Sub CheckBox1_Click()
> CheckBox2 = Locked
> CheckBox3 = Locked
> CheckBox4 = Locked
> CheckBox5 = Locked
> CheckBox6 = Locked
>
> End Sub
>
> Private Sub CheckBox2_Click()
> CheckBox1 = Locked
> CheckBox3 = Locked
> CheckBox4 = Locked
> CheckBox5 = Locked
> CheckBox6 = Locked
>
> End Sub
>
> Private Sub CheckBox3_Click()
> CheckBox1 = Locked
> CheckBox2 = Locked
> CheckBox4 = Locked
> CheckBox5 = Locked
> CheckBox6 = Locked
>
> End Sub
>
> Private Sub CheckBox4_Click()
> CheckBox1 = Locked
> CheckBox2 = Locked
> CheckBox3 = Locked
> CheckBox5 = Locked
> CheckBox6 = Locked
>
> End Sub
>
> Private Sub CheckBox5_Click()
> CheckBox1 = Locked
> CheckBox2 = Locked
> CheckBox3 = Locked
> CheckBox4 = Locked
> CheckBox6 = Locked
>
> End Sub
>
> Private Sub CheckBox6_Click()
> CheckBox1 = Locked
> CheckBox2 = Locked
> CheckBox3 = Locked
> CheckBox4 = Locked
> CheckBox5 = Locked
>
> End Sub
>
> 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 = ""
> CheckBox1 = False
> CheckBox2 = False
> CheckBox3 = False
> CheckBox4 = False
> CheckBox5 = False
> CheckBox6 = False
>
> TextBox1.SetFocus
>
> Else
> Unload Me
> End If
>
> End Sub
>
>
>

Adeptus - ExcelForums.com · 07-09-2005, 09:05 AM

> 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 · 07-09-2005, 02:05 PM

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/
_______

Adeptus - ExcelForums.com wrote:

>>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!
>