Some sample code, ListBox can be substituted for CombBox in the coe
From a different Workbook that is open
Option Explicit
Private Sub UserForm_Initialize()
Dim rSource As Range
'Set reference to the range of data to be filled
Set rSource = Workbooks("Source.xls").Worksheets("Sheet1").Range("b2:b6")
Me.ListBox1.RowSource = rSource.Address(external:=True)
End Sub
From a different Workbook that is closed
Private Sub UserForm_Initialize()
Dim oWbSource As Workbook
Dim ListItems As Variant
Dim i As Integer
With Me.ListBox1
.Clear ' remove existing entries from the listbox
' prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set oWbSource = Workbooks.Open("C:\FolderName\MyWorkbook.xls", _
False, True)
' get the values you want
ListItems = oWbSource.Worksheets(1).Range("a2:a50").Value
oWbSource.Close False ' close the source workbook without saving changes
Set oWbSource = Nothing
Application.ScreenUpdating = True
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i)
Next i
.ListIndex = -1
End With
End Sub
Option Explicit
Private Sub UserForm_Initialize()
Dim oWbk As Workbook
Dim rdata As Range
Application.ScreenUpdating = False
'check if workbook containing source is open if not open it.
If Not WorkbookOpen("D:\Foldername\MyWorkbook.xls") Then
Set oWkbk = Workbooks.Open("D:\Foldername\MyWorkbook.xls")
Else: Set oWkbk = Workbooks("D:\Foldername\MyWorkbook.xls")
End If
'this is the data to load to combobox
Set rdata = oWkbk.Worksheets("MASTER ").Range("A5:A103")
With Me.ComboBox1
.Clear 'clear any previous data
.Style = fmStyleDropDownList
.BoundColumn = 0
'set RowSource
.RowSource = rdata.Address(external:=True)
'0 = no selection
.ListIndex = 0
End With
Set oWbk = Nothing
Set rdata = Nothing
Application.ScreenUpdating = True
End Sub
Bookmarks