Hey guys/gals,
I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. Any thoughts on how to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Here is the code in Module 1:
Here is the code on the sheet that has the button to start the Survey Form:
*EDITED TO ADD IN THE "CODE,/CODE" LINES
I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. Any thoughts on how to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Code:
Private Sub button_next_Click()
''confirm there is a name
If TextBox1.Value = "" Then
MsgBox ("Please enter your name")
Exit Sub
End If
''confirm there is an answer
If rda.Value = False And rdb.Value = False Then
MsgBox ("Please select an answer")
Exit Sub
End If
''enter the name, question, and answer into results array
results(questionnumber - 1, 0) = QASurvey.TextBox1.Value
results(questionnumber - 1, 1) = questionnumber
If rda.Value = True Then
results(questionnumber - 1, 2) = "A" 'could put rda.caption
Else
results(questionnumber - 1, 2) = "B"
End If
''set the radio button to empty
rda.Value = False
rdb.Value = False
''populate the form with the next question
If questionnumber = UBound(info) + 1 Then
''stop
Call enterresults
MsgBox ("Thank You for completing the survey")
Unload QASurvey
Else
''populate next question
QASurvey.Label_question.Caption = info(questionnumber, 0)
QASurvey.rda.Caption = info(questionnumber, 1)
QASurvey.rdb.Caption = info(questionnumber, 2)
End If
questionnumber = questionnumber + 1
End Sub
Sub enterresults()
Sheets("results").Select
Range("a2:c1000").ClearContents
''loop through results array
r = 2
For i = 0 To UBound(results)
Cells(r, 1).Value = results(i, 0)
Cells(r, 2).Value = results(i, 1)
Cells(r, 3).Value = results(i, 2)
r = r + 1
Next i
End Sub
Private Sub UserForm_Initialize()
Sheets("questions").Select
r = Range("a1").End(xlDown).Row - 1
ReDim info(r - 1, 3) 'redim 2 dimensional array
ReDim results(r - 1, 2) 'redim 2 dimensional array
'' fill a 2d array
r = 2
i = 0
Do Until Cells(r, 1).Value = ""
info(i, 0) = Cells(r, 1).Value
info(i, 1) = Cells(r, 2).Value
info(i, 2) = Cells(r, 3).Value
r = r + 1
i = i + 1
Loop
''populate the userform with data
QASurvey.Label_question.Caption = info(0, 0)
QASurvey.rda.Caption = info(0, 1)
QASurvey.rdb.Caption = info(0, 2)
questionnumber = 1
End SubHere is the code in Module 1:
Code:
''global variables
Public info() As Variant
Public results() As Variant
Public questionnumber As IntegerHere is the code on the sheet that has the button to start the Survey Form:
Code:
Private Sub Start_Button_Click()
QASurvey.Show
End Sub*EDITED TO ADD IN THE "CODE,/CODE" LINES
Last edited:
