If you have a list in one sheet that you want populated into a combobox in another sheet, one of the easiest ways would be to name the range using VBA.
Name a dynamic range from another sheet using VBA
Dim LstRws As Long, Rng As Range, Sht2 As Worksheet, r
Set Sht2 = Worksheets("Sheet2")
LstRws = Sht2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Sht2.Cells(2, 1), Sht2.Cells(LstRws, 1))
ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:=Rng
This will use the Range from Sheet2 A2 to The last cell in column A and name it List
Adding additional code will populate a combo box from the controls tool bar or populate a data validation drop down menu.
Sub PopComboboc()
Dim LstRws As Long, Rng As Range, Sht2 As Worksheet, r
Set Sht2 = Worksheets("Sheet2")
LstRws = Sht2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Sht2.Cells(2, 1), Sht2.Cells(LstRws, 1))
ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:=Rng
r = Sht2.Range("List").Value
Worksheets("Sheet1").ComboBox1.List = r
End Sub
Sub PopDataValidation()
Dim F1 As Range
Dim LstRws As Long, Rng As Range, Sht2 As Worksheet, r
Set Sht2 = Worksheets("Sheet2")
Set F1 = Worksheets("Sheet1").Range("F1")
LstRws = Sht2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Sht2.Cells(2, 1), Sht2.Cells(LstRws, 1))
ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:=Rng
With F1
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=List"
End With
End With
End Sub
Find more help on excel here
http://davesexcel.com/
