populate combo box from worksheet

[tracktraining]

Hi All,

I am new to excel vba coding. I hope you can help me out. I have created a
combo box in Userform. Now I need to populate the data. The data is stored in
sheet1 of service.xls file. How can I populate my combo box with the data in
the service.xls file? I would use the .AddItem but there are a lot of data.

Thanks,
Tracktraining

 

[Dave Peterson]

Maybe...

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myCell As Range

'Service.xls has to be open!
With Workbooks("service.xls").Worksheets("sheet1")
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

'can you use the entire range of values?
Me.ComboBox1.List = myRng.Value

'or if you wanted to loop
For Each myCell In myRng.Cells
Me.ComboBox1.AddItem myCell.Value
Next myCell

'or if you wanted to check a value
'and add the item from a different column
For Each myCell In myRng.Cells
If LCase(myCell.Value) = "some value here" Then
Me.ComboBox1.AddItem myCell.Offset(0, 1).Value
Next myCell

End Sub

 

[tracktraining]

thanks Dave!

Is there a way to do it without having the service.xls open?

thanks again.

 

[Dave Peterson]

You could use a bunch of formulas in a hidden sheet that point back at the cells
you want brought back.

There are other ways, but that seems easiest to me.

=======
On the other hand, your code could open the workbook, add the info to the
combobox, close the workbook.

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myCell As Range
Dim ServWkbk as workbook

application.screenupdating = false 'hide it from the user

set servwkbk = nothing
on error resume next
set servwkbk = workbooks.open(filename:="C:\folder\services.xls", _
readonly:=true)
on error goto 0

if servwkbk is nothing then
msgbox "file not found"
else
'Service.xls has to be open!
With servwkbk.worksheets("Sheet1")
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

'can you use the entire range of values?
Me.ComboBox1.List = myRng.Value

'or if you wanted to loop
For Each myCell In myRng.Cells
Me.ComboBox1.AddItem myCell.Value
Next myCell

'or if you wanted to check a value
'and add the item from a different column
For Each myCell In myRng.Cells
If LCase(myCell.Value) = "some value here" Then
Me.ComboBox1.AddItem myCell.Offset(0, 1).Value
Next myCell
end if

application.screenupdating = true

End Sub

(untested, uncompiled. watch for typos!)

thanks Dave!

Is there a way to do it without having the service.xls open?

thanks again.

 

[Dave Peterson]

And I forgot the close statement in the code!

...all that code
servwkbk.close savechanges:=false '<--- added
application.screenupdating = true

End Sub