MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to show a ListBox with a spreadsheet

  Asked By: Ted    Date: Dec 01    Category: MS Office    Views: 2233

Anybody can help me ?

I need to show the columns with the names (COD, NAME, PHONE) and show the datas,
but in the form, then only show one date (NAME).

In the spreadsheet (clientes) I´ve three columns
Code Name Phone

Following the code below.

Private Sub UserForm_Activate()
Dim SourceWB As Workbook
Dim ListItems As Variant
Dim i As Integer
Application.ScreenUpdating = False

With Me.ComboBox1
.Clear ' remove existing entries from the combobox
' open the source workbook as ReadOnly

Set SourceWB = Workbooks.Open("C:\TEMP\Clientes.xls", _
False, True)
'no need to use all rows if empty
ListItems.AddItem = SourceWB.Worksheets(1).Range("b2:B50").Value

' get the values you want
SourceWB.Close False ' close the source workbook without saving
Set SourceWB = Nothing
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox

Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item

End With
Application.ScreenUpdating = True
End Sub



1 Answer Found

Answer #1    Answered By: Aaleyah Khan     Answered On: Dec 01

You don't say what's actually happening when you run this code. When I put it
in a small test workbook  I get an "object required" error on the

ListItems.AddItem = SourceWB.Worksheets(1).Range("b2:B50").Value

statement. This is because ListItems is not a control and therefore doesn't
have an AddItem method.

In your description, you have columns  A, B and C, but you only ever use column

I think you need to give us a more detailed explanation of what you are trying
to do.

I'm also not sure what you expect the Transpose function call to do when used
with VBA variables.

Didn't find what you were looking for? Find more on How to show a ListBox with a spreadsheet Or get search suggestion and latest updates.