Check Box checking

[Steve]

Hi

I have 12 check boxes for each month.

Is there a quick way of looping through all the check boxes to see if they
are ticked or not?

I then want to write the info to an array.

Thanks

Steve

 

[JLGWhiz]

You want to know what? Which ones are checked, how many are checked, if any
at all are checked, if none are checked. Pick one.

 

[Steve]

Which ones are checked

You want to know what? Which ones are checked, how many are checked, if any
at all are checked, if none are checked. Pick one.

 

[Rick Rothstein \(MVP - VB\)]

Where are the CheckBoxes at... directly on the worksheet or on a UserForm?
If on the worksheet, where did they come from... the Forms Toolbar or the
Visual Basic toolbar?

Rick

 

[Steve]

Apologies

They are on a user form

Where are the CheckBoxes at... directly on the worksheet or on a UserForm?
If on the worksheet, where did they come from... the Forms Toolbar or the
Visual Basic toolbar?

Rick

 

[Rick Rothstein \(MVP - VB\)]

Something like this maybe (using a CommandButton to enact the code for
example purposes only)...

Private Sub CommandButton1_Click()
Dim Cnt As Long
Dim Ctrl As Control
Dim ChkBxArray() As String
ReDim ChkBxArray(1 To 12)
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Value Then
Cnt = Cnt + 1
ChkBxArray(Cnt) = Ctrl.Name
End If
End If
Next
ReDim Preserve ChkBxArray(1 To Cnt)
' Prove it worked
For Cnt = 1 To UBound(ChkBxArray)
Debug.Print ChkBxArray(Cnt)
Next
End Sub

Rick

 

[Norman Jones]

Hi Steve,

Try something like:


'==========>>
Option Explicit
Dim arr(1 To 12) As Boolean

'------------>>
Private Sub CommandButton1_Click()
Dim Ctrl As msforms.Control
Dim i As Long

For Each Ctrl In Me.Controls
With Ctrl
If TypeOf Ctrl Is msforms.CheckBox Then
i = i + 1
arr(i) = .Value
End If
End With
Next Ctrl

End Sub

'------------>>
Private Sub CommandButton2_Click()
MsgBox arr(1)
End Sub
'<<==========

 

[Steve]

Many Thanks Rick

I'll give it a go

Steve

Something like this maybe (using a CommandButton to enact the code for
example purposes only)...

Private Sub CommandButton1_Click()
Dim Cnt As Long
Dim Ctrl As Control
Dim ChkBxArray() As String
ReDim ChkBxArray(1 To 12)
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Value Then
Cnt = Cnt + 1
ChkBxArray(Cnt) = Ctrl.Name
End If
End If
Next
ReDim Preserve ChkBxArray(1 To Cnt)
' Prove it worked
For Cnt = 1 To UBound(ChkBxArray)
Debug.Print ChkBxArray(Cnt)
Next
End Sub

Rick

 

[Steve]

Many Thanks Norman

I'll give it a go.

Appreciate your time.

regards

Steve