Name ranges using VBA for ComboBox

[Lane Smith]

I would like to Populate a combobox with a named range. The named
range is from a different sheet, and will be named using VBA because
it always changes

Thanks alot

 

[Jim Bean]

Try This
'*******************************************
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
'*********************************************************
Find it here
http://davesexcelblogs.blogspot.com/

 

[Lane Smith]

Try This
'*******************************************
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
'*********************************************************
Find it herehttp://davesexcelblogs.blogspot.com/

Thanks alot, that does work. I did forget to mention it was for a
userform but with a slight alteration I got it to work.