Populate listbox on userform from another workbook

[ipbr21054]

I have a userform which has a Listbox.
I would like to populate this Listbox with customers names another workbook.
The selected Listbox name would then be placed in Textbox1

Some info for you for the external workbox etc

Workbook is called DR
Worksheet in question is called POSTAGE
Customers names are in column B
The population wouldnt need to list ALL from column B but say last row & up 10 rows

 

[ipbr21054]

UPDATE
I should of said COMBOBOX not LISTBOX

So now i have this code in use but please can you advise how i have it from last row up for column B

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
ComboBox1.List = .Sheets("POSTAGE").Range("B100:B150").Value
.Close 0
End With

 

[ipbr21054]

With the following i get the RTE message Could not set the list property.

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
ComboBox1.List = .Sheets("POSTAGE").Cells(Rows.Count, 1).End(xlUp).Row

.Close 0
End With

 

[Engberg]

Without knowing exactly how Comboboxes work, what happens if you change to this? It grabs the last row with values in col B and the 10 rows above it (untested code)

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
set s = .Sheets("POSTAGE")
Set b2 = s.Cells(Range("B" & Rows.Count).End(xlUp).Row, "B")
Set b1 = b2.Offset(-10, 0)
ComboBox1.List = s.Range(b1, b2)
.Close 0
End With

 

[ipbr21054]

Hi,
I have applied that but get variable not defind.
set s line of code is highlighted
I assume it will do the same for the next two.

Not sure how or what to write to define it

 

[Engberg]

Does this fix it?

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
Dim s As Worksheet, b1 As Range, b2 As Range
Set s = .Sheets("POSTAGE")
Set b2 = s.Cells(Range("B" & Rows.Count).End(xlUp).Row, "B")
Set b1 = b2.Offset(-10, 0)
ComboBox1.List = s.Range(b1, b2)
.Close 0
End With

 

[ipbr21054]

Hi,
I haveapplied that but now i get COULD NOT SET THE LIST PROPERTY, INVALID PROPERTY ARRAY INDEX
The issue is the line of code in Red shown below

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
Dim s As Worksheet, b1 As Range, b2 As Range
Set s = .Sheets("POSTAGE")
Set b2 = s.Cells(Range("B" & Rows.Count).End(xlUp).Row, "B")
Set b1 = b2.Offset(-10, 0)
ComboBox1.List = s.Range(b1, b2)
.Close 0
End With

 

[Engberg]

I tested it and got the same error, adding .Value after the range fixes it:

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
Dim s As Worksheet, b1 As Range, b2 As Range
Set s = .Sheets("POSTAGE")
Set b2 = s.Cells(Range("B" & Rows.Count).End(xlUp).Row, "B")
Set b1 = b2.Offset(-10, 0)
ComboBox1.List = s.Range(b1, b2).Value
.Close 0
End With

 

[ipbr21054]

That got past the error but it doesnt work as it should for me.
This is the code i have in use.

    With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
        Dim s As Worksheet, b1 As Range, b2 As Range
        Set s = .Sheets("POSTAGE")
        Set b2 = s.Cells(Range("B" & Rows.Count).End(xlUp).Row, "B")
        Set b1 = b2.Offset(-10, 0)
        ComboBox1.List = s.Range(b1, b2).Value
       .Close 0
End With

On my worksheet i am currently at row 2207 BUT when i look at the 10 customers values the code has put in the ComboBox its row 603 & then 9 others.
This is way back up my list & not the last 10

 

[Engberg]

Ye, I didn't think to actually test with two different files as you have, found the issue.

With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
        Dim s As Worksheet, b1 As Range, b2 As Range
        Set s = .Sheets("POSTAGE")
        Set b2 = s.Cells(s.Range("B" & s.UsedRange.Rows.Count + 1).End(xlUp).Row, "B")
        Set b1 = b2.Offset(-10, 0)
        ComboBox1.List = s.Range(b1, b2).Value
       .Close 0
End With