Can one list box determine which list shows up in the second list box? 6

[RITec]

I am trying to figure out a way to have one list box determine what is in the second list box

Example:

First List Box:
Chevy
Ford
Dodge

Say Chevy was selected

The second list box would say:
Camaro
Corvette
Monte Carlo
Etc...

Can anyone help me with this? %-)

 

[KenReay]

Hi

OK so let us say your lists are hled in two tables:

tblMakes
strMaker PK

tblModels
strMaker ) PK
strModel )

You base you first list box or combo box on tblMakes

You base your second combo box or list box on tblModels, but with a critiera of strMaker = cboMakes

In the got focus event of the second combo or list box you put code which checks that the first combo (or list) has a value, if not give message and setfocus to first combo (or list), if firstr does have a value, then requery second eg

If IsNull(cboMaker) Then
MsgBox "Please select Maker"
cboMaker.SetFocus
Else
cboModel.Requery
End if Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[RITec]

Hi Ken

I apologize for not being more clear in the original post.
I have a need for this in both Excel and Access. I am somewhat new to both, this looks to be for Access (If I am wrong please let me know)and I will try it as soon as I get to work. Thank You for responding

 

[Wray69]

Hi

If this is a validation list in excel you could have your first list in a1 containing,

Chevy
Ford
Dodge

Then make list of IF statements ie,
=IF(a1="Chevy","camaro",IF(a1="Ford","mustang",IF(a1="Dodge","Stratus"," &quot)) You would need to make a IF statement for each model, then after you have those made place them into another validation list.

Hope this helps.

Wray

 

[GlennUK]

Hi RITec,
in Excel, using named lists will allow you to automatically have control over the contents of a second list dependant on the choice made in a first list.

Make a list of the manufacturers, e.g.
Chevy
Ford
Dodge
as you probably already have.
Also make lists for the models of each manufacturer, and name each list to be the name of the manufacturer, e.g. make
Camero
Corvette
Monte Carlo
be named as Chevy.
Now, when you have a cell with the list of manufacturers as the Data Validation, ( Data/Validation/List etc ), use the contents of that cell to be the list in another cell that you want have displayed a list of models, and use this control as the list definition ...
=INDIRECT(A2)
assuming that A2 has the manufacturer.
This will show the models specific to the manufacturer chosen.

Hope this is what you want.
Glenn.

 

[GlennUK]

PS, to create a name for a range, select the range, and do menu command Insert/Name/Define and type the name required. ( there are a few ways to define names, but this will do for now ).

Glenn.

 

[RITec]

I want to thank everyone for there time. the last couple of days have been very busy for me. I am new to access and excel and this site makes it a whole lot easier to get things done.

Once again thanks to everyone.

 

[krupa]

Hi I am doing the same thing. And i get it to work with the named ranges, but if i have chevy in cell A and Camero in cell B and i change Cell A to Ford, Camero is the value of Cell B, even though the list is correct. How can I prevent this. I want to clear the value of cell B when cell A is changed.

 

[GlennUK]

Hi Krupa,
without writing macros to do it you can't clear a cell when another cell is changed. Assuming you want something simple, why don't you just have an error check formula next to the cell B, so that when anyone changes cell A, an error message appears in cell C until a correct choice is made in cell B.
Such a formula might look like this ...

=IF(B2="","",IF(ISNA(MATCH(B2,INDIRECT(A2),0)),"** ERROR - Incorrect entry ***","&quot)

Glenn.

 

[krupa]

Thanks Glenn, that's a good idea. i was trying to avoid the worksheet_change sub. it complicates alot of what I am doing with macros.


Again thanks!

 

[TurboBill]

All of your post's were very helpful, I hank you very much. I found Wray69's the easies fo my needs though so and extra thanks out to Wray69.

 

[RITec]

Hello Everyone
These examples in both excel and access have been a great help to me.

Just in case anyone was wondering about the Access version (Thank You KenReay) I did make a little change and moved the requery to afterupdate on the cbomaker and ended up with the following.


Private Sub cbomaker_AfterUpdate()
CboModel = "" 'Clears the model Combo box
CboModel.Requery 'Requery the model Combo box
CboModel.SetFocus 'Moved focus to model Combo box
End Sub

and left the GotFocus cbomodel with

Private Sub cbomodel_GotFocus()
'Check to see if cbomaker is empty
If IsNull(cbomaker) Then
MsgBox "Please select Maker"
cbomaker.SetFocus 'Move focus to Maker combo box
End If
End Sub


If there is a better way to do this please let me know.

I am always looking for better (easier) ways to do things.

 

[RITec]

I ended up hiding cboModel until Maker was selected

Private Sub cbomaker_AfterUpdate()
cboModel.visible = true
CboModel = "" 'Clears the model Combo box
CboModel.Requery 'Requery the model Combo box
CboModel.SetFocus 'Moved focus to model Combo box
End Sub