how to design a form in Excel like this?

[EMR]

Dear all

I want design a questionnaire in Excel with form programming.

there are about twenty questions and one question in one form. User click
button "Next" and "back" to complete those questions.

Now, my question is how to design those forms?

insert 20 forms or insert 1 forms and others created in the code??

thanks.

 

[Rick Rothstein \(MVP - VB\)]

I want design a questionnaire in Excel with form programming.

there are about twenty questions and one question in one form. User click
button "Next" and "back" to complete those questions.

Now, my question is how to design those forms?

insert 20 forms or insert 1 forms and others created in the code??

I wouldn't do it that way. I would use one UserForm, one Label, four
OptionButtons (I'm assuming a multiple choice test) and three
CommandButtons... and that's all. I would use arrays inside the code to
track the current question and answers and simply change what is displayed
in response to the user's actions. Here is a simple framework from which you
can build such a project. Add a UserForm to the project and place the
controls (in the numbers I indicated above) on the UserForm. Put the Label
at the top of the form, place the four OptionButtons (in numerical order
according to their default names), one under the other, beneath the Label
and place the three CommandButtons at the bottom of the form, in numerical
order according to their default names) from left to right. Finally,
copy/paste the code below my signature into the UserForm's code window.
That's it... run the UserForm and take the test. Note that for this example,
I used only 3 questions, but you should be able to see how to extend that if
you look at the layout I provided. Oh, and normally, I would store the test
questions and answer in an encrypted text file to be read in and decoded in
the UserForm's Initialize event.

Rick

Dim CurrentQuestion As Long
Dim UsersAnswers() As Long
Dim TestAnswers() As String
Dim TestQuestions() As String
Const cTotalTestQuestions = 3
Const cAnswersPerQuestion = 4

Private Sub UserForm_Initialize()
' Set up stuff for this example...
' these can be done at design time
CommandButton1.Caption = "Previous"
CommandButton2.Caption = "Next"
CommandButton3.Caption = "Done"
' Set up stuff required for the program
CurrentQuestion = 1
ReDim TestQuestions(1 To cTotalTestQuestions)
ReDim TestAnswers(1 To cTotalTestQuestions, 1 To cAnswersPerQuestion)
ReDim UsersAnswers(1 To cTotalTestQuestions)
TestQuestions(1) = "1. Which country is the largest in area?"
TestAnswers(1, 1) = "Cuba"
TestAnswers(1, 2) = "United States"
TestAnswers(1, 3) = "China"
TestAnswers(1, 4) = "Brazil"
TestQuestions(2) = "2. What is the nearest value to PI?"
TestAnswers(2, 1) = "3-1/7"
TestAnswers(2, 2) = "4*ArcTan(1)"
TestAnswers(2, 3) = "3.1416"
TestAnswers(2, 4) = "22/7"
TestQuestions(3) = "3. How many sides are in an octagon?"
TestAnswers(3, 1) = "2"
TestAnswers(3, 2) = "4"
TestAnswers(3, 3) = "6"
TestAnswers(3, 4) = "8"
Call UpdateDisplay
End Sub

Private Sub CommandButton1_Click()
Call StoreCurrentAnswer
CurrentQuestion = CurrentQuestion - 1
If CurrentQuestion = 0 Then CurrentQuestion = cTotalTestQuestions
Call UpdateDisplay
End Sub

Private Sub CommandButton2_Click()
Call StoreCurrentAnswer
CurrentQuestion = CurrentQuestion + 1
If CurrentQuestion = cTotalTestQuestions + 1 Then CurrentQuestion = 1
Call UpdateDisplay
End Sub

Private Sub CommandButton3_Click()
Dim X As Long
Dim Answer As Long
Dim Message As String
Call StoreCurrentAnswer
Answer = MsgBox("Have you reviewed your answers and are you " & _
"satisfied with them?", vbYesNo, "Test Completed?")
If Answer = vbYes Then
CommandButton1.Enabled = False
CommandButton2.Enabled = False
' Here I am simply displaying the answers. You,
' of course, would score the test at this point.
For X = 1 To cTotalTestQuestions
If UsersAnswers(X) = 0 Then
Message = Message & "Question " & CStr(X) & ": <<BLANK>>"
Else
Message = Message & "Question " & CStr(X) & ": " & _
TestAnswers(X, UsersAnswers(X))
End If
If X < cTotalTestQuestions Then Message = Message & vbNewLine
Next
MsgBox Message
Unload Me
End If
End Sub

Sub StoreCurrentAnswer()
Dim X As Long
For X = 1 To cAnswersPerQuestion
If Controls("OptionButton" & CStr(X)).Value = True Then
UsersAnswers(CurrentQuestion) = CLng(Right$(Controls( _
"OptionButton" & CStr(X)).Name, 1))
Exit For
End If
Next
End Sub

Sub UpdateDisplay()
Dim X As Long
If UsersAnswers(CurrentQuestion) = 0 Then
For X = 1 To cAnswersPerQuestion
Controls("OptionButton" & CStr(X)).Value = False
Next
Else
Controls("OptionButton" & CStr(UsersAnswers( _
CurrentQuestion))).Value = True
End If
Label1.Caption = TestQuestions(CurrentQuestion)
For X = 1 To cAnswersPerQuestion
Controls("OptionButton" & CStr(X)).Caption = _
TestAnswers(CurrentQuestion, X)
Next
End Sub

 

[EMR]

thanks Rick!
it's very kind help.

and, as I told, it's for a customer survey project.
and customers will complete this form in separate PCs.

So I would like to store the questions and answers in the spreadsheets.
at the end of this project, I'll collect those spreadsheets to my PC and
employ ADO (this module is embedded in the spreadsheets) to transfer those
data to the Access database.

yes, you suggest to store the questions and answers in an encrypted text
file.
It seems clearer in the system structure.
So, it raises new question:
1) sorry, I don't know what's an encrypted text file? could you kindly
please give me a simple example?
2) use vba to read & write & delete & modify this file both in Excel and
Access. (I only have the experience of ADO)
3) my questions are not one by one, it means the next question is determined
by the previous answer.
in other words, every defined answer has an important attribute: the
index of next question.
could an encrypted text file handle such a table??

thanks for your attention

GF Dong

 

[Rick Rothstein \(MVP - VB\)]

When I mentioned the encryption, I was (for some reason) thinking this might
be a test questionnaire where it would be necessary to keep the questions
and possible answer a secret before hand (unencrypted text can usually be
read from an EXE file in a plain text viewer program such as Notepad).
However, as I now see it, this is a simple customer questionnaire where
secrecy is not important. Given that, you can forget about my suggestion
regarding encryption. However, if you are still interested in "what it is",
then a Google search on the word 'encryption' should help.

I would still use an approach similar to the one I laid out for your forms
and control... there is no need to load up 20 different forms and attendant
controls just to present 20 questions to a user.

Rick