fancy forms
 

ok, so i have managed to create my form with several txt fields and cbo boxes
and cmd buttons. when you click 'OK' puts that information into various
cells in a s/s.

Q1
is it possible to use validation rules in the cmb boxes on the form??
example
first cbo list fruit, vegetable, drink
depending on which one you select here ie 'fruit' the second cbo shows a
list of fruit only, and so on for the next cbo. i have a total of 4 cbo
boxes to drop down to.

and if this is possible i am hoping the next wish list might be.

Q2
if the user was smart and new what they wanted in the 4th cbo and they went
to select that then the 1st, 2nd, 3rd cbo auto populates with the relevant
information.

any help on either of these questions would be great

cheers Pete

fancy forms
 

Hi,

Hope the following example is what you want re Q1 i.e selection from one
combobox will populate another combobox with a list of data.

(I'll have to think about Q2 which is about "reversing" the code below!)

Combobox1 is initiliazed with list of car manufacturers based on named range
"Manufacturers".

When combobox1 is selected, combobox2 is loaded with models from named
range; if manufacturer is "Ford" then named range is "Ford_Models".
Similarly, combobox3 is loaded with colours for a given model e.g. named
range "Fiesta_Colours" (UK car models here!)

You could use arrays of named ranges in combination with the ListIndex of a
combobox (or both) to get the date to be loaded.I personally think named
ranges are the best way of organising the data.

HTH

Private Sub Combobox1_Change()
Dim rng As Range, cell As Range
' Set combobox2 with list of models for given manufacturer
Set rng = Range(ComboBox1.Value & "_Models")
ComboBox2.Clear
For Each cell In rng
ComboBox2.AddItem cell.Value
Next cell
End Sub

Private Sub Combobox2_Change()
Dim rng As Range, cell As Range
' Set combobox3 with list of colours for given model
Set rng = Range(ComboBox2.Value & "_Colours")
ComboBox3.Clear
For Each cell In rng
ComboBox3.AddItem cell.Value
Next cell
End Sub

Private Sub Userform_Initialize()
Dim rng As Range, cell As Range
Set rng = Range("Manufacturers")
For Each cell In rng
ComboBox1.AddItem cell.Value
Next cell
End Sub



"Little pete" wrote:

ok, so i have managed to create my form with several txt fields and cbo boxes
and cmd buttons. when you click 'OK' puts that information into various
cells in a s/s.

Q1
is it possible to use validation rules in the cmb boxes on the form??
example
first cbo list fruit, vegetable, drink
depending on which one you select here ie 'fruit' the second cbo shows a
list of fruit only, and so on for the next cbo. i have a total of 4 cbo
boxes to drop down to.

and if this is possible i am hoping the next wish list might be.

Q2
if the user was smart and new what they wanted in the 4th cbo and they went
to select that then the 1st, 2nd, 3rd cbo auto populates with the relevant
information.

any help on either of these questions would be great

cheers Pete