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
