Creating multiple checkboxes in a cell

[Pat]

I want to add 2 checkboxes in one cell, similar to a yes/no checkbox but name
one contract and one permanent so that the person entering the data chooses
either or in that cell in order to identify if an employee is a permanent
employee or not. Can this be done in excel?

 

[Dave Peterson]

You could do it, but as a user, I wouldn't like it.

I'd want a single checkbox: Permanent?
I could check it for true or uncheck it for false.

I don't want to have to worry about checking both boxes or even leaving both
unchecked and not knowing what that employee was.

If you really want two options, how about a couple of option buttons from the
Forms toolbar surrounded by a groupbox from that same toolbar.

Only one of those optionbuttons can be chosen at a time.

 

[Pat]

If I chose to use a couple of optionbuttons how do I get them into the same
cell.
I do want/need one or the other checked for each entry.

 

[Dave Peterson]

Carefully place them in the cell.

You could also use some code to make life easier (and prettier):

Option Explicit
Sub testme01()

Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set wks = Worksheets("sheet1")

With wks
'nice for testing
.OptionButtons.Delete
.GroupBoxes.Delete

Set myRng = .Range("a1:A10")
For Each myCell In myRng.Cells
With myCell
Set GrpBox = .Parent.GroupBoxes.Add(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
GrpBox.Caption = ""
GrpBox.Visible = False

Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
Left:=.Left, _
Width:=.Width / 2, _
Height:=.Height)
OptBtn.Caption = ""
OptBtn.LinkedCell = .Address(external:=True)

Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
Left:=.Left + (.Width / 2), _
Width:=.Width / 2, _
Height:=.Height)
OptBtn.Caption = ""

.NumberFormat = ";;;"

End With
Next myCell
End With
End Sub

It also assigns the linked cell to the cell holding the optionbuttons--but with
a format of ;;;, you don't see it in the worksheet--but you can select the cell
and look at the formulabar to see 1 or 2.

And then you can use:

=countif(a1:a10,1)
=countif(a1:a10,2)
to count the number of each option.

====
It still looks like a checkbox solution to me, though.

If I chose to use a couple of optionbuttons how do I get them into the same
cell.
I do want/need one or the other checked for each entry.

 

[Pat]

Thank you I will give it a go.
--
Thanks, Pat

Carefully place them in the cell.

You could also use some code to make life easier (and prettier):

Option Explicit
Sub testme01()

Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set wks = Worksheets("sheet1")

With wks
'nice for testing
.OptionButtons.Delete
.GroupBoxes.Delete

Set myRng = .Range("a1:A10")
For Each myCell In myRng.Cells
With myCell
Set GrpBox = .Parent.GroupBoxes.Add(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
GrpBox.Caption = ""
GrpBox.Visible = False

Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
Left:=.Left, _
Width:=.Width / 2, _
Height:=.Height)
OptBtn.Caption = ""
OptBtn.LinkedCell = .Address(external:=True)

Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
Left:=.Left + (.Width / 2), _
Width:=.Width / 2, _
Height:=.Height)
OptBtn.Caption = ""

.NumberFormat = ";;;"

End With
Next myCell
End With
End Sub

It also assigns the linked cell to the cell holding the optionbuttons--but with
a format of ;;;, you don't see it in the worksheet--but you can select the cell
and look at the formulabar to see 1 or 2.

And then you can use:

=countif(a1:a10,1)
=countif(a1:a10,2)
to count the number of each option.

====
It still looks like a checkbox solution to me, though.