Hi do any of you guys know how to make a second combo box contens b
dependant on the value selected in the first combo box. I'm don't kno
if it is possible but it sounds reasonable enough but i have no ide
how to do it. Please hel

--
Message posted from http://www.ExcelForum.com

Noctos,

It depends how you use it. By this, I mean that you could have the source
data for the second combobox segregated either as separate tables of data,
and load the appropriate table as the first combo value changes, or have one
table of data with an identifier to say which item goes with which selection
from combo 1.

This is an example that I have that populates a second Data Validation cell
(not combobox, but easily adapted) dependent upon the value selected from a
first DV cell.

Firstly, we have a workbook open code in ThisWorkbook to initialise

Private Sub Workbook_Open()
Dim cell As Range

LoadListNames

Application.DisplayAlerts = False

PopulateList1
PopulateList2 1

Application.DisplayAlerts = True

End Sub

And then this is the code associated with the DVs

Option Explicit

Public Sub LoadListNames()
Dim oWsData As Worksheet
Dim cRows As Long, cCols As Long, i As Long, j As Long

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo load_exit

'create dynamic range names for List1 and List2 lists
cCols = data.Cells(1, Columns.Count).End(xlToLeft).Column
Set oWsData = data
With oWsData
For i = 2 To cCols
cRows = .Cells(Rows.Count, i).End(xlUp).Row
ThisWorkbook.Names.Add Name:="List2_" & i - 1, _
RefersToR1C1:="='Data'!R2C" & i & ":R" &
cRows & "C" & i
Next i
End With
ThisWorkbook.Names.Add Name:="List1Values", _
RefersToR1C1:="='Data'!R1C2:R1C" & cCols

load_exit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

Public Function PopulateList1()
Dim i As Long

Application.EnableEvents = False
'On Error GoTo pl1_exit

With master.cboList1
.Clear
For i = 2 To Range("List1Values").Count + 1
.AddItem data.Cells(1, i).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl1_exit:
Application.EnableEvents = True

End Function


Public Function PopulateList2(idx As Long)
Dim i As Long
Dim formula As String

Application.EnableEvents = False
'On Error GoTo pl2_exit

formula = "List2_" & CStr(idx)

With master.cboList2
.Clear
For i = 1 To Range(formula).Count
.AddItem Range(formula).Cells(i, 1).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With

pl2_exit:
Application.EnableEvents = True
End Function


This assumes the data is on a worksheet called Data, and structured like so
List1 USA UK France
List2 Alabama Bedfordshire Ain
Alaska Berkshire Aisne
Arizona Buckinghamshire Allier
Arkansas Cambridgeshire AlpesdeHaute-Provence
California Cheshire Alpes-Maritimes
Colorado Cornwall Ardèche
Connecticut Cumberland Ardennes
Delaware Derbyshire Ariege
Florida Devonshire Aube
Georgia Dorset Aude
Hawaii Durham Aveyron
Idaho Essex Bas-Rhin
Illinois Gloucestershire Bouches-du-Rhône
Indiana Greater London Calvados
Iowa Hampshire Cantal
Kansas Herefordshire Charente
Kentucky Huntingdonshire Charente-Maritime
Louisiana Kent Cher
Maine Lancashire Corrèze
Maryland Leicestershire Corse-du-sud[A]
Massachusetts Lincolnshire Côtesd'Amor[Côtes-du-Nord]
Michigan Norfolk Côte-d'Or
Minnesota Northamptonshire Creuse
Mississippi Northumberland Deux-Sèvres
Missouri Nottinghamshire Doubs
Montana Oxfordshire Dordogne
Nebraska Rutland Drôme
Nevada Shropshire Essonne
New Hampshire Somerset Eure
New Jerse Staffordshire Eure-et-Loir
New Mexico Suffolk Finistère
New York Surrey Ille-et-Vilaine
North Carolina Sussex Indre
North Dakota Warwickshire Indre-et-Loire
Ohio Westmorland Gard
Oklahoma Wiltshire Gers
Oregon Worcestershire Gironde
Pennsylvania Yorkshire Haute-Corse[b]
Rhode Island Haute-Garonne
South Carolina Haute-Loire
South Dakota Haute-Marne
Tennessee Haute-Saône
Texas Hautes-Alpes
Utah Haute-Savoie
Vermont Hautes-Pyérnées
Virginia Haute-Vienne
Washington Haut-Rhin
West Virginia Hauts-de-Seine
Wisconsin Hèrault
Wyoming Isère
Jura
Landes
Loire
Loire-Atlantique
Loir-et-Cher
Loiret
Lot
Lot-et-Garonne
Lozère
Maine-et-Loire
Manche
Marne
Mayenne
Meurthe-et-Moselle
Meuse
Monaco[Principality]
Morbihan
Moselle
Nievre
Nord
Oise
Orne
Pas-de-Calais
Puy-de-Dôme
Pyrénées-Atlantiques,PaysBasque
Pyrénées-Orientales
Rhône
Saône-et-Loire
Sarthe
Savoie
Seine-Maritime
Seine-et-Marne
Seine-Saint-Denis
Somme
Tarn
Tarn-et-Garonne
TerritoiredeBelfort
Val-de-Marne
Val-d'Oise
Var
Vaucluse
Vendée
Vienne
Ville-de-Paris
Vosges
Yonne
Yvelines


If you want a sample, write to me directly (note my signature below) and I
will send this version, and a combobox version.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Noctos" wrote in message
...
Hi do any of you guys know how to make a second combo box contens be
dependant on the value selected in the first combo box. I'm don't know
if it is possible but it sounds reasonable enough but i have no idea
how to do it. Please help


---
Message posted from http://www.ExcelForum.com/

hmm i'm new i don't know how to post message to individuals who aren't
logged on and your explnation was a moe than i needed thanks but coulld
you possibly give send me the example to my email adressat
so i could understand how it worked a bit mire.


---
Message posted from http://www.ExcelForum.com/

I'll just get it up together and post it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Noctos" wrote in message
...
hmm i'm new i don't know how to post message to individuals who aren't
logged on and your explnation was a moe than i needed thanks but coulld
you possibly give send me the example to my email adressat
so i could understand how it worked a bit mire.


---
Message posted from http://www.ExcelForum.com/

hey bob is there a way of using combo boxes and the vlookup formulae to
produce the same results as using your dynamic system


---
Message posted from http://www.ExcelForum.com/