I am trying to create an excel based questionnaire. I am using the drop down buttons for users to select their responses.
However, what I want to do for the first question to guide the questions for the entire survey.

For eg. for the first question- the user is asked which state he/she lives in. Based on the response, it will determine whether it is the South, west, East, north, north east etc. So if a user selected California, he would get to answer questions related to the West Coast only. The other questions to the other regions would not populate. How can this be done?
Also, at the end of this questionnaire, I want to collate all the responses in another tab that reads like a narrative.

Any help is appreciated.

Thanks

Hi,

Am Mon, 3 Jun 2013 08:07:53 -0700 (PDT) schrieb RB:

For eg. for the first question- the user is asked which state he/she lives in. Based on the response, it will determine whether it is the South, west, East, north, north east etc. So if a user selected California, he would get to answer questions related to the West Coast only. The other questions to the other regions would not populate. How can this be done?
Also, at the end of this questionnaire, I want to collate all the responses in another tab that reads like a narrative.

have a look:
http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html
http://www.contextures.com/xlDataVal15.html


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, June 3, 2013 10:16:59 AM UTC-5, Claus Busch wrote:
Hi,



Am Mon, 3 Jun 2013 08:07:53 -0700 (PDT) schrieb RB:



For eg. for the first question- the user is asked which state he/she lives in. Based on the response, it will determine whether it is the South, west, East, north, north east etc. So if a user selected California, he would get to answer questions related to the West Coast only. The other questions to the other regions would not populate. How can this be done?

Also, at the end of this questionnaire, I want to collate all the responses in another tab that reads like a narrative.



have a look:

http://www.contextures.com/xlDataVal13.html

http://www.contextures.com/xlDataVal02.html

http://www.contextures.com/xlDataVal15.html





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Thanks

One possible approach...

I do similar projects for various clients, and so I've developed a
Questionaire structure (template) over the years. Here's how it
works...

User selects 1st option from a dropdown on the entry 'page'. This page
also contains directions for how to proceed filling out the form
'pages'. Each form 'page' also has (what displays as) a 'help' button
that when selected a MsgBox pops up with info pertaining to use of that
page.

A set of Qs/Opts 'pages' appear related to that choice by toggling
visibility of local scope named ranges (the form's 'pages') containing
various 1st option Qs/Opts.

Some selections cause other sub-ranges to appear (as an indented list)
with more options specific to that selection. (This behaves like a HTML
expand section, where deselecting the option also collapses the
sub-range)

All of this is event driven, meaning no controls are used anywhere on
the worksheet. I do, though, have cells formatted to look/behave/feel
like buttons, checkboxes, or comboboxes because these don't shift with
all the visibility toggling like Form/AX controls would in this
scenario. VBA runs all this.

Background colors are used as desired. Gridlines and row/col headings
are turned off. As mentioned, worksheet events drive the functionality
so the sheets behaves just like an interactive form being filled in,
but without having to jump around to different sections every time an
option choice is changed.

In your case, the simplest approach would be to put 'area' Qs in named
ranges and display the appropriate range based on the state selected in
the area dropdown on the startup 'page'. I'd use delimited strings
(stored in an array) that are named for each 'area', and just loop
through each string to determine which 'page' to display. This would be
a 2D array where the named range for each 'area' is stored in dim1, and
its associated delimited string of states is stored in dim2...

Const sAreas$ = "SW,NW,SE,NE,N,S,E,W"
Const sStates$ = "CA,NM,AZ,NV:OR,MO,ND,SD,WY"..and so on

Dim saAreas(1 To NumAreas, 1 To 1)
Dim vAreas, vStates, n%, k%

'Load the areas/states
vAreas = Split(sAreas, ","): vStates = Split(sStates, ":")
For n = LBound(vAreas) To UBound(vAreas)
k = k + 1
saAreas(k, 1) = vAreas(n): saAreas(K, 2) = sStates1
Next 'n

...so the data can be accessed as follows...


For n = LBound(saAreas) To UBound(saAreas)
If InStr(1, saAreas(n, 2), Range(OptState).Value) 0 Then
Range(AllPages).Rows.Hidden = True
Range(saAreas(n, 1)).Rows.Hidden = False
SetSubRanges saAreas(n, 1): Exit For
End If
Next 'n

...to toggle your 'pages' and their respective sub-ranges. Note that the
named range names are stored in saAreas(n, 1), and their associated
states are arranged in the same order as they apply to the areas listed
in sAreas. So in the example, area 'SW' is assigned states
"CA,NM,AZ,NV" while area 'NW' is assigned states "OR,MO,ND,SD,WY". (I'm
Canadian and so I'll leave the actual area assignments to you!<g)
What's important is that the delimited string order is syncronized.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion