VBA, Userform with checkboxes - How to change the value from "Yes" to "1"

[jpoppet]

Hi all

I'm creating a UserForm which will add data to my worksheet.
I currently have checkboxes, which once the data is added the value of a checked box equals "Yes" and if unchecked it is "No"

Is there anyway I can tell excel to change the added value from "Yes" to "1" and "No" to "0"?

Otherwise, the easiest way will just be to add a Text Box and manually enter in the numbers.

This is used so I can calculate the amount of times somebody was late, sick or on holiday.

Thanks

[Norie]

You could use something like this, which will put 1 in C10 if the checkbox is checked, 0 otherwise.

Please Login or Register  to view this content.

PS Are you sure your checkboxes return Yes/No? All of mine return True/False.

[Solus Rankin]

With if statements.

Please Login or Register  to view this content.

[jpoppet]

You're right they are "TRUE" and "FALSE"
What does "Range" refer to? If I use C10, will every new field added overwrite the existing values in C10 or will it carry on with adding it to the next available row?

*EDIT* If I remove the cell so only "" shows will this keep it in flow with adding to last row?

Please Login or Register  to view this content.

[Norie]

C10 is a hard coded reference I used in the example code I posted.

I don't know where you want the 1/0 to go so change it however you need to.

[jpoppet]

I have added this;

Please Login or Register  to view this content.

but it's came up with this message;
EXCELHELP.PNG

When the Data is added it finds the last row and adds it there, is the range able to be limited to a collumn?

[Norie]

What code were you using previously to put the data from the userform on the worksheet?

PS Range("") is wrong.

[jpoppet]

Here's the full code.
I'm dubious of deleting the frame_click but I'm pretty sure I don't need them?

Please Login or Register  to view this content.

Obviously you can see that I've changed the range, I created a name range for each column but it's just added all values in all rows of each column :P

[Norie]

Why have you created the ranges?

You should just need to change this code,

Please Login or Register  to view this content.

like this.

Please Login or Register  to view this content.

Also, I think you can get rid of this, and the names.

Please Login or Register  to view this content.

PS It should be OK to delete the empty subs like Frame1_Click, in fact if you goto Debug>Compile Project they might disappear.