Solved: Count checkboxes that were selected from a userform

[jsullivan]

Hello all. First time poster, long time lurker! :hi:
Been lurking over the forums for a while, when I need a hint or trick, and find VBAX very handy. I'm fairly novice to VBA and programming in general, but am finding my way through it thanks to job requirements that have been added to me.

My question is:

I'm looking for a way to verify if multiple checkboxes have been selected on a form. My form has the default userform1 name, and the checkboxes are CheckBox1 through CheckBox12 (I may of course add on in the future).

If so, just a simple msgbox "multiple choices have been selected" will work, so the issue I'm having is, how do I programatically have it check all my checkboxes for true without typing the following all the way to 12. What if I have 50 checkboxes in the future? I'd rather just have one command to do them all.

this is what I have in module right now to check for multiples



Dim count as integer
'Check for multiple checboxes
If UserForm1.CheckBox1 = True Then
count = count + 1
End If
If UserForm1.CheckBox2 = True Then
count = count + 1
End If
If UserForm1.CheckBox3 = True Then
count = count + 1
End If
If count > 1 Then
MsgBox ("You have selected multiple items")
End If

[fumei]

If you just want a count of the number checkboxes that are checked, this is code for a commandbutton:


Option Explicit

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim j As Long
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.CheckBox Then
If Me.Controls(ctl.Name).Value = True Then
j = j + 1
End If
End If
Next
MsgBox j & " checkboxes are checked."
Unload Me
End Sub


If you require knowing which ones, it could be something like:


Private Sub CommandButton1_Click()
Dim ctl As Control
Dim j As Long
Dim msg As String
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.CheckBox Then
If Me.Controls(ctl.Name).Value = True Then
msg = msg & ctl.Name & vbCrLf
j = j + 1
End If
End If
Next
msg = j & " checkboxes are checked." & _
vbCrLf & vbCrLf & msg
MsgBox msg
Unload Me
End Sub


Result (for example):

2 checkboxes are checked

Checkbox2
Checkbox4

[jsullivan]

Ahhh thank you much, that is very helpful.

I have a similar situation where I want to create a boolean variable in the Module if the count is >3.

I'm trying to modify the code to fit that but how do I pass the variable from the userform to the module?

[fumei]

Make your variable global.

In the standard module:

Public myCounter (or whatever name) As Long

In the userform module:

myCounter = x

"how do I pass the variable from the userform to the module?" This is a bit confusing. The userform has its own module. I am not sure if you are asking about passing a variable value to another module. If you are using the variable in the userform module (i.e. the userform is still loaded), then put the variable, still as Public, at the top of the userform module.

Userform module

Option Explicit
Public j As Long ' your counter variable
' OR if you want to use a boolean
Public YeahSure As Boolean


Private CommandButton1_Click()
blah blah blah

your other userform procedures.

[fumei]

If the userform is still loaded, and if the variable is PUBLIC, then you can use it anywhere, in any procedure in the userform module.

If you are talking about having the variable have the value in a different module (i.e. not the userform module), then make it a Public variable in that module.

You want to have a boolean outside the procedure that is doing the counting? So it can be used by a different procedure?

[jsullivan]

The userform is still loaded, but maybe I'm getting my terminology wrong. I apologize for that. Hopefully I can explain.

I have a "Sub Autonew" Module1 that kicks off with a template.
The autonew loads the form.
When the form is complete, I hide the Userform1 (not unload)
and the Module1 continues.
I would like the boolean variable inside Module1 to use to insert specific text if a variable is true (more than 3 checkboxes checked).

[fumei]

1. Unless you have a specific reason to use AutoNew, it may be better to use Document_New. Are you creating a new document from a template?

2. If your standard module is named "Module1", then that is where you put your Public variable. So, for example:

' in Module 1
Public bolMoreThan3 As Boolean
' the the userform module
' using a commandbutton event

Sub CommandButton1_Click()
Dim ctl As Control
Dim j As Long
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.CheckBox Then
If Me.Controls(ctl.Name).Value = True Then
j = j + 1
End If
End If
If j > 3 Then
bolMoreThan3 = True
Exit For
End If
Next
End Sub

This counts through the checkboxes, adding to a counter. When the counter is greater than 3, it makes the Public boolean (bolMoreThan3) true, and stops counting (exiting the For loop).

3. WHY are you hiding, not unloading, the userform?

[jsullivan]

1. Unless you have a specific reason to use AutoNew, it may be better to use Document_New. Are you creating a new document from a template?
yes, creating a new document from a template



3. WHY are you hiding, not unloading, the userform?

I have an error check (there is a required dropdown and a required textbox field) that I have in my Module1, that refers the user back to the form if either of those properties are blank.

[jsullivan]

And btw thank you, I guess I didn't realize that you can make a variable public like that.

[fumei]

"I have an error check (there is a required dropdown and a required textbox field) that I have in my Module1, that refers the user back to the form if either of those properties are blank."

Why is that in Module 1 instead of the userform module? What is the purpose of doing this?

[jsullivan]

"I have an error check (there is a required dropdown and a required textbox field) that I have in my Module1, that refers the user back to the form if either of those properties are blank."

Why is that in Module 1 instead of the userform module? What is the purpose of doing this?

No purpose to it, other than I had no other code in my userform, so I wasnt sure I wanted to put it on CommandButton1, but I see now where that would be the sensible place to put it.

[fumei]

Hmmmmm. Unless there is a specific reason for doing so (and there are some) code that relates to a userform, should be in the userform module.