PDA

View Full Version : Check box question



austenr
04-19-2010, 12:15 PM
Ive looked on the forum and net and can't find an answer to this one. Say you have three check boxes and you and to display in a textbox the ones that were checked, how do you do it?

mdmackillop
04-19-2010, 12:42 PM
Hi Austen,
Userform or ActiveX?

austenr
04-19-2010, 12:45 PM
Hi Malcomb,

Userform...

lucas
04-19-2010, 02:29 PM
Would a listbox work just as well?

Private Sub CommandButton1_Click()
Dim x As String
Dim ctrl As Control
Dim test As String
ListBox1.Clear
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = True Then
x = ctrl.Caption & "....Checked"
ListBox1.AddItem x
End If
End If
Next ctrl
End Sub

austenr
04-19-2010, 02:33 PM
That'l work. Thanks Steve.

lucas
04-19-2010, 02:35 PM
And if you want a report on all of them whether checked or not:

'lists all checkboxes and whether checked or not
Private Sub CommandButton1_Click()
Dim x As String
Dim ctrl As Control
Dim test As String
ListBox1.Clear
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = True Then
x = ctrl.Caption & "....Checked"
ListBox1.AddItem x
Else
x = ctrl.Caption & "....not checked"
ListBox1.AddItem x
End If
End If
Next ctrl
End Sub

GTO
04-19-2010, 03:11 PM
Well... I didn't think I could actually get slowwweeer at typing (or thinking for that matter), but evidently sleep deprivation still counts...

Anyways, looks well answered, but just in case it needs to be a textbox or label...


Option Explicit

Private Sub CheckBox1_Click()
Me.TextBox1.Value = CheckBoxes_Ret
End Sub

Private Sub CheckBox2_Click()
Me.TextBox1.Value = CheckBoxes_Ret
End Sub

Private Sub CheckBox3_Click()
Me.TextBox1.Value = CheckBoxes_Ret
End Sub

Private Sub UserForm_Initialize()

With Me
.CheckBox1.Caption = "chk_1"
.CheckBox2.Caption = "chk_2"
.CheckBox3.Caption = "chk_3"
End With
End Sub

Private Function CheckBoxes_Ret() As String
Dim _
ctrl As Control, _
COL As Collection, _
ary As Variant, _
i As Long

Set COL = New Collection
COL.Add "DUMMY", "DUMMY"

For Each ctrl In Me.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value Then
On Error Resume Next
For i = 1 To COL.Count
If UCase(ctrl.Name) < UCase(COL(i)) Then
COL.Add ctrl.Name, CStr(ctrl.Name), COL(i)
End If
Next
COL.Add ctrl.Name, CStr(ctrl.Name), COL(i)
On Error GoTo 0

End If
End If
Next

COL.Remove "DUMMY"

If Not COL.Count > 0 Then
CheckBoxes_Ret = "Nothing checked"
Else
ReDim ary(1 To COL.Count)
ReDim ary(1 To COL.Count)
For i = 1 To COL.Count
ary(i) = COL(i)
Next
CheckBoxes_Ret = Join(ary, ", ")
End If
End Function

A great day to 'all ya'll',

Mark