in order

[the hotspur amigo]

I have a spreadsheet that has a list of questions then in the next cell is the space for their answer.

Is it possible to force the users I am going to send this questionnaire too to answer the questions in the correct order? so they cant skip a question as that messes up the outcome.

and help would be grateful. at the moment I am thinking maybe a possible IF statement. along the lines of IF blank etc. I am hoping I can get a dialogue box to pop up with a error message. but that bit might just be a pipe dream.

any help please. even ideas if you don't have a answer would be great.

(all my answers are validated to be yes or no if that helps)

 

[DominicB]

Good afternoon the hotspur amigo

Strictly speaking, no. But you could use a formula that will not show the question until they have answered the previous one.

=IF(C24<>"","Is black the opposite of white?","")

In this example assume that cell C24 holds the answer to your first question - question 2 will not be revealed until C24 contains something. You could use Data > Validation to force the users to type yes / no.

However there is nothing from stopping the user from going back and changing their answer...

HTH

DominicB

 

[the hotspur amigo]

thats what i have done so far using =+vlookup formula but they can still type in a answer when the question isnt there yet. its the possability of that i was hopeing to stop from happening.

does that make it any more clear?

 

[mark.wagner]

well, you "can", but it's going to involve a lot of locking, unlocking, making visible, etc.

you might consider prompting with messageboxes. It could be a simple loop, changing the questions and recording the answers as it loops through cells on the sheet. probably easier to code that turning all that stuff off and on and worrying what to do if they want to go back.

 

[the hotspur amigo]

all cells are locked in the spreadsheet except the 8 cells where they can put the answers. but they can enter 'yes or no' in any of them 8 cells in any order.

im not good enogh with loops to do what you suggested so might have to leave it as it is if that is the only real possability

 

[BBHRVS]

'hi;
'my advise would be to manage this through a userform or with a macro running behind
'the worksheet. In both cases, at the beginning you hide everything but the first
'question. Once this one is answered, you show the second one...and further on.
'At the end you create a validation sub in order to verify everything has been
'correctly answered.
'Take care also that users are very creative, so you should allow the choice only
'between yes and no if you want only these answers (why not option buttons or
'validation loaded in the cells). Of course, as mentionned, by Mark, this is a little bit cumbersome, but is working. In that case, I locked the answer once done in order to avoid any correction... brutal, isn't it ?

'let's imagine range A1 is your 1st answer and A5 the last one and macro running on the whst

Private Sub Worksheet_Activate()
ActiveSheet.Rows("2:5").EntireRow.Hidden = True
ActiveSheet.Cells.Locked = True
ActiveSheet.Range("A1:A5").Locked = False
ActiveSheet.Range("A1:A5").ClearContents
ActiveSheet.ScrollArea = "A1:A5"
ActiveSheet.Protect userinterfaceonly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
ActiveSheet.Rows(2).EntireRow.Hidden = False
Target.Locked = True
ElseIf Target.Address = "$A$2" Then
ActiveSheet.Rows(3).EntireRow.Hidden = False
Target.Locked = True
'to be continued....
End If
End Sub

 

[mark.wagner]

something like this gets the job done. questions are typed in column A starting with row 1. answers will be stored in column B.

Private Sub Run_Question()

Dim Q As Integer 'number of questions
Dim curr As Integer 'current question

Q = 4 'in this case i have 4 questions
curr = 1 'start with question #1

For curr = 1 To 4

Worksheets(1).Cells(curr, 2).Value = InputBox(Worksheets(1).Cells(curr, 1).Value)

Next 'loop thru the questions

End Sub

of course, I just read that the answers are yes/no, so using the msgbox function instead of inputbox and capturing the proper button response will do the trick there.
then you can just hide the entire sheet from user tinkering, they are restricted to yes/no answers, are required to answer in the correct order etc etc.

good luck and don't be afraid to experiment with VB code. learning it will be invaluable.

 

[mark.wagner]

to use a yes/no messagebox modify as follows:

Worksheets(1).Cells(curr, 2).Value = MsgBox(Worksheets(1).Cells(curr, 1).Value, vbYesNo)

yes will be =6 and no will = 7 in the answer column.