Help with getting excel column into vba listbox

[Anonymous]

I would like to have a column in Excel appear in a lisbox on my user form. I can create a buuton and on user click it populates the box, but I want the box to be filled in without clicking.

Any ideas?

Below is what I have so far:
' FORM INTIALIZES

Private Sub frmMain_Initialize()

Call Set_Up_Excel 'connects to excel

Dim objRange As Excel.Range
Dim xValue As Variant

Set objRange = excelSheet.Range("A1:A40")

xValue = objRange.Value

ListBox1.ColumnCount = objRange.Columns.Count
ListBox1.List = xValue

End Sub

thanks

Don Sauvage

[Anonymous]

haven't tried it but...

Set objRange = excelSheet.Range("A1:A40")

'isn't this just the value of the first cell in the
range?

xValue = objRange.Value

'wouldn't you be wanting row.count not column
count

your range only has one column in it.

'I was thinking something like (not sure of the
syntax, just an idea)

Dim i as Integer

For i = 1 to objRange.Rows.Count

'or

For i = 1 to Rows(objRange)

  ListBox1.AddItem objRange.Row(i).value

  'or

  ListBox1.AddItem objRange.Offset(i,0).value

  'or ???

next i

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

"dsauvage" <
href="mailto:dsauvage@compsource.com">dsauvage@compsource.com> wrote in
message
href="news:f172c09.-1@WebX.maYIadrTaRb">news:f172c09.-1@WebX.maYIadrTaRb...

I
would like to have a column in Excel appear in a lisbox on my user form. I can

[Anonymous]

Try this way. I've split into two parts, the Userform_Initialise which then calls the Excelstart function. The data is on Sheet
5 in the range A1-A21


'Read data in from EXCEL sheet
Sub ExcelStart()
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
If Err Then
MsgBox "Cannot start Excel", vbCritical, "Error Starting Excel"
Unload Me
End If
End If
ExcelApp.Visible = True
Set Workbook = ExcelApp.Workbooks.Open("J:\Development\willerby materials.xls")
Set WorkSheet = ExcelApp.Worksheets(5)
ExcelApp.Visible = False

End Sub



'Load the data into the list box on initialisation
Private Sub UserForm_Initialize()
ExcelStart

With cboMaterial
.ColumnCount = 2
.List = WorkSheet.Range("A1:A21").Value
.ListIndex = 0
End With
End Sub


HTH

John Bilton

[Anonymous]

>
> 'isn't this just the value of the first cell in the range?
> xValue = objRange.Value
>

Range.Value will return a 1-d or 2-d array if the Range has multiple cells.
So you'd want to modify yours to be:

ListBox1.AddItem objRange.Row(i).Resize(1,1).value
'or
ListBox1.AddItem objRange.Offset(i,0).Resize(1,1).value


This is a little cleaner:

For i = 1 to objRange.Rows.Count
ListBox1.Additem objRange.Cells(i,1).value
next 'i

If you need to reverse the order, just add "Step -1" to your "For" statement

Good luck,


James

[Anonymous]

Is it necessary to make the ExcelApp.Visible=true or can you have it take excel values "behind the scenes"

[Anonymous]

What happened when you tried it? Did you get a particular error message?

Thx