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
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)
