ComboBox1 rowsource from different workbook

[Mr.Karr]

Hello

Can someone please provide me a code snippet to take rowsource from a different workbook. Below code works fine with a range, but it would be great if you can help to get a range for combobox. Thanks in advance.

 With GetObject("\\100.25.1965.100\Data\Consolidate\NewFolder\Data.xlsx")
          .ComboBox1.List = Range("B1:B1500")
          .Close 0
    End With

 

[p45cal]

I don't know where this code is (userform code-module, sheet code-module?) but it may not matter. Assuming the range B1:B1500 is within Data.xlsx you need to qualify the sheet the data is on:

With GetObject("\\100.25.1965.100\Data\Consolidate\NewFolder\Data.xlsx")
  ComboBox1.List = .Sheets("TheSheetNameHere").Range("B1:B1500")
  'if you don't know the name of the sheet then you might get away with the following line if there is only one sheet in Data.xlsx (dangerous if there are more sheets visible in Data.xlsx):
  'ComboBox1.List = .ActiveSheet.Range("B1:B1500")
  .Close 0
End With

This may need tweaking depending on where the code is and where ComboBox1 is. Perhaps better to see all the code of the macro.