UserForm with option buttons for a survey

**JimmyG.** · 06-19-2013, 06:39 PM

I have been searching all over the internet for how to do this but all the tutorials seem to reference text boxes and not option buttons.

Basically I am trying to create a survey type form that will be used to collect data about the type of phone calls employees receive - if you can tell me the basics I can build it from there.

There will be a list of question ie
1. "Existing customer" Option 1 = Yes, Option 2 = No
2. "Number of times they have phoned" Option 1 = 0, Option 2 = 1, Option 3 = 1+

These wont be the questions but need to know how the code is build so I can build the whole form.

If the form was created with the questions as above, how would this then be transferred to the spreadsheet as below, rows 1 and 2 are the headings, then after that the data is entered as per the users selections

A B C D E F
1 Exisiting customer Times phoned
2 Yes No 0 1 1+
3 1 1
4 1 1
5 1 1

When the buttons have been selected and the user clicks submit it needs to add to the next free row. Also if it could also timestamp at the end that would be great to.

Alternatively, could you also let me know how to have the data entered to accumulated, again based on the above to look like the below (but the timestamp wouldn't be needed):

A B C D E F
1 Exisiting customer Times phoned
2 Yes No 0 1 1+
3 1 2 1 1 1

This then could be build up throughout the day for full total.

If this is something that someone could help me with that would be fantastic

**scott.s.fower** · 06-20-2013, 02:39 PM

first off, for the question existing customer i would use a checkbox which defaults as yes or no, then say your check box is named extcustchk and your form is called survey then you would reference that box by survey.extcustchk.value

to get it to input a value to your spreadsheet for calculus then you would use

if survey.extcustchk.value = true then 
range("A" & NextArow).value = 1
else:
range("A" & NextArow).value = 0
end sub

where NextArow is a variable based off of NextArow= CStr(Range("A" & CStr(Application.Rows.Count)).End(xlUp).Row) + 1

the option buttons work the same way they have a name and are contained within the form survey.optionbuttonname.value
but if you want the value to be in a single cell in your data set you should do something like

if survey.optionbuttonname.value = true then n = 1
if survey.optionbuttonname2.value = true then n = 2
if survey.optionbuttonname3.value = true then n = 3

where = 1 is the same as saying true meaning it is selected
then the next line would be

range("A" & NextArow).offset(,1).value = n

hope that makes sense

example for option button.xlsm

**JimmyG.** · 06-22-2013, 06:06 AM

Thanks Scott

**JimmyG.** · 06-22-2013, 06:15 AM

One more thing, in terms of the Ok button, is there a way to have it add the data but then keep the survey open so the next set of answers can be entered without having to click to open the form again?