PDA

View Full Version : Solved: Turning Checkboxes into Numeric Values



Juicey27
09-07-2010, 08:30 AM
Excel 2007 - VBA Coding
I have a userform that when the submit button is clicked dumps the data collected in the form onto a spreadsheet. Currently the checkboxes dump a "True/False" statement onto the spreadsheet. Userform has 24 checkboxes. I am wondering where/if I can have the "True/False" produce either a 1,0,-1 (number) based on the selection, rather then the "True/False." There are eight series of three checkboxes. Each series has a Yes-Checkbox, a No-Checkbox, a N/A checkbox. I'm thinking a Yes=1, No=-1, N/A=-1 from the overall count. (As in the question does not count as it doesn't apply.) The overall score is based on eight questions, or however many apply. (See attachment1 for Userform, this attachment also includes the VBA coding for the submit button on the userform) :banghead:

Bob Phillips
09-07-2010, 09:07 AM
Something like this?



Range("A4").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = cboNames.Value
ActiveCell.Offset(0, 5) = IIf(CheckBox1.Value, 1, IIf(CheckBox2.Value, 0, IIf(CheckBox3.Value, -1, "")))
ActiveCell.Offset(0, 6) = IIf(CheckBox4.Value, 1, IIf(CheckBox5.Value, 0, IIf(CheckBox6.Value, -1, "")))
ActiveCell.Offset(0, 7) = IIf(CheckBox7.Value, 1, IIf(CheckBox8.Value, 0, IIf(CheckBox9.Value, -1, "")))
ActiveCell.Offset(0, 8) = IIf(CheckBox10.Value, 1, IIf(CheckBox11.Value, 0, IIf(CheckBox12.Value, -1, "")))
ActiveCell.Offset(0, 9) = IIf(CheckBox13.Value, 1, IIf(CheckBox14.Value, 0, IIf(CheckBox15.Value, -1, "")))
ActiveCell.Offset(0, 10) = IIf(CheckBox16.Value, 1, IIf(CheckBox17.Value, 0, IIf(CheckBox18.Value, -1, "")))
ActiveCell.Offset(0, 11) = IIf(CheckBox19.Value, 1, IIf(CheckBox20.Value, 0, IIf(CheckBox21.Value, -1, "")))
ActiveCell.Offset(2, 5) = txtComments.Value
ActiveCell.Offset(0, 1) = DTPicker1.Value
ActiveCell.Offset(0, 3) = cboCoaches.Value

p45cal
09-07-2010, 10:29 AM
I imagine the three checkboxes for each question are mutually exclusive? If so, do you have code behind them to ensure this? If not, may I suggest either you use option buttons which can easily be made mutually exclusive without code, or to use a single togglebutton, which can have three states corresponding to your n/a,yes/no. This last would mean you only have to look at as many controls as there are questions to get a score.
I've started a bit of exploration of this but need to see some feedback to be sure I'm not on the wrong track.

Juicey27
09-07-2010, 10:55 AM
Exactly what I am looking for. Thank you XLD

Juicey27
09-07-2010, 10:57 AM
Thank you for the suggestions p45cal. I think I'm goin to roll with XLD above reply. Thanks for looking at the situation.