Count Checked status of Checkbox

[Maneesh Massey]

Hello,
I have a userform set up such that it has a 4 CheckBoxes on it. How can I Count the number of Checked CheckBoxes when the user checkmarks it on the form and depending on the checked status, it writes either a TRUE or FALSE value to the worksheet. I would like to count only the checked values of the Checkboxes on the userform with a formula entered through VBA code. How can this be done ? Thanks for all responses.
Regards,

 

[Hui]

Maneesh

Try the following, assuming that the Check Boxes link to cells A1:A4

Set myRange = Range("A1:A4")
x = WorksheetFunction.CountIfs(myRange, "True")

 

[Maneesh Massey]

Thanks for helping once again Hui. Was just curious, will the same code work for a dynamic named range or needs some alteration? I haven't tried it yet, but I'll work through it today.

 

[Hui]

Say you had a Named Formula as
myRange: =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),1)

then use

x = WorksheetFunction.CountIfs([myRange], "True")

No need for the Set line

 

[Hui]

In the original case you could have used:

x = WorksheetFunction.CountIfs([A1:A4], "True")

 

[Maneesh Massey]

I will try this today and let you know. Thank you for the quick response.