Multi Column List Box ? Cant load data

[jammy Bodger]

Hi,
I am improving a quotation template in word that I have already created. I want to be able to use a user form with a multi column list box that will be populated with Product Data from an Excel spreadsheet.

Eventually I would like to add the product data to the word document via bookmarks. However, I am stuck in that I can’t figure out why the code I’ve been working on won’t populate the list box. Because the spreadsheet will be created every week, I wish to avoid using named ranges if possible. Here’s my code:

' Set a reference to Microsoft Excel Object Library

Private Sub UserForm_Initialize()

Dim objExcel As New Excel.Application
Dim ArticleList As Excel.Workbook
Dim FileName As String
Dim DataRange As Long
Dim i As Long

FileName = "S:\Etimex Forms\Worksheet in Basis (1).xls" ' set the file string

Set ArticleList = objExcel.Workbooks.Open(FileName) ' open the file
DataRange = Range("A65536").End(xlUp).Rows.Count - Range("A65536").End(xlUp).Row + 1 ' look for all cells that contain data

ListBox1.ColumnCount = 3 ' set the number of columns

With Me.ListBox1
For i = 1 To DataRange ' populate the Listbox
.AddItemRange("A65536").End(xlUp).Rows.Cells(i, 1)
.List(.ListCount - 1, 1) = Range("A65536").End(xlUp).Rows.Cells(i, 2)
.List(.ListCount - 1, 2) = Range("A65536").End(xlUp).Rows.Cells(i, 3)
Next i

End With

Set ArticleList = Nothing
objExcel.Quit
Set objExcel = Nothing

End Sub

Any help or comments would be greatly appreciated.

Thank you, James

[mdmackillop]

I've assumed your data is in a "block", but DataRange could be set by other methods if required.
' Set a reference to Microsoft Excel Object Library

Private Sub UserForm_Initialize()

Dim objExcel As New Excel.Application
Dim ArticleList As Excel.Workbook
Dim FileName As String
Dim DataRange As Excel.Range

FileName = "S:\Etimex Forms\Worksheet in Basis (1).xls" ' set the file string

Set ArticleList = objExcel.Workbooks.Open(FileName) ' open the file
Set DataRange = Range("A1").End(xlDown).CurrentRegion

ListBox1.ColumnCount = 3 ' set the number of columns
ListBox1.List = DataRange.Value

Set ArticleList = Nothing
objExcel.Quit
Set objExcel = Nothing

End Sub

[jammy Bodger]

:clap: Brilliant, thanks for such a quick response. Just tested the revised code and it loads the number columns I specify which is great. Can I push my luck and I ask if the following is possible:

The sheet that the data is coming from will be created every week from scratch from SAP data. I would like to avoid spending time rearranging the columns as there is approximately 40. So could I specify the columns I want on the word document? That way I?m guaranteed I get the correct columns in the listbox without a laborious weekly column arranging.

Thanks again, James

[fumei]

If this is being executed in Word, will:Set DataRange = Range("A1").End(xlDown).CurrentRegion actually work? Should that Range not be explicitly named? As in:Set DataRange = ArticleList.Worksheets("Sheet1").Range("A1").End(xlDown).CurrentRegion

[jammy Bodger]

Hi fumei,
Thank you for your input, this also produces the same result.

[mdmackillop]

So could I specify the columns I want on the word document?
Private Sub UserForm_Initialize()

Dim objExcel As New Excel.Application
Dim ArticleList As Excel.Workbook
Dim FileName As String
Dim DataRange As Excel.Range
Dim Data As String

Data = InputBox("Enter first column letter")

FileName = "S:\Etimex Forms\Worksheet in Basis (1).xls"' set the file string

Set ArticleList = objExcel.Workbooks.Open(FileName) ' open the file
Set DataRange = ArticleList.Worksheets("Sheet1").Range(Data & "1").End(xlDown)
Set DataRange = Range(DataRange, DataRange.End(xlDown).Resize(, 3))

ListBox1.ColumnCount = 3 ' set the number of columns
ListBox1.List = DataRange.Value

Set ArticleList = Nothing
Set DataRange = Nothing
objExcel.Quit
Set objExcel = Nothing

End Sub

[mdmackillop]

If this is being executed in Word, will:Set DataRange = Range("A1").End(xlDown).CurrentRegion actually work? Should that Range not be explicitly named? As in:Set DataRange = ArticleList.Worksheets("Sheet1").Range("A1").End(xlDown).CurrentRegion
Hi Gerry,
It does work, but I suspect only if the data is in Sheets(1). Being explicit in the address is much more robust. :thumb

[jammy Bodger]

Hi mdmackillop,

Thanks for the rework of the code. I have two points; the first is that the code now only returns the last item in the row of the column. Two, what I was looking for is to have the columns already in the code i.e. A1,F1,N1 rather than have any user input.

I have been looking and trying to possibly use other methods, mainly the Union Method alas without success. Do you think this is a possibility or am I going completely in the wrong direction? :dunno

[mdmackillop]

You could add a combobox to list the available dataranges. I don't see how you can select the data to be displayed without some user input, or are you looking for all data ranges to be listed, one below the other?

[jammy Bodger]

or are you looking for all data ranges to be listed, one below the other?
Okay thanks for the info and letting me know what is possible. I don't want to go down the route of user input because knowing my users; I know that they won't remember to select the correct columns. The quotations are auto saved and I think that I will end up getting lots of empty quotations which the user has abandoned, because they have selected the wrong columns. It was my intention to use the same spreadsheet for use in other documents that I will be creating. Hence it would have been ideal to select the correct columns in the code.

Stepping back out a bit I will create a new user-defined table in SAP that can be saved in a SAP profile which will have the correct columns in the correct order that I want. This does mean that I will have to create a few data tables; each one will be for a particular type of document. It takes about five minutes to load the data into the table, but I'm sure that this will be a good excuse for the user whose responsibility it is to go and make a cup of tea or coffee!

Thanks for all your help mdmackillop, I?m relatively new at VBA so I?m sure I?ll be making other post when I encounter a hurdle I can?t get around. One more thing, could you please clarify what you meant in the quoted text as I can?t exactly visualise what your question means.

[mdmackillop]

You mention using the Union method. The only thing that occurred to me was that you wanted to join the data from columns A, F & N to display in the list box.

[jammy Bodger]

I had a think about the problem over the weekend and experimented. I have now found a solution that works… as you will see in my code below I have used the union method and manipulated the excel sheet to get the columns in the right order for them to display correctly on the listbox.

The next thing is to do something with the displayed data! I’m sure I’ll be posting again if I get stuck.


' Set a reference to Microsoft Excel Object Library
' Explicitly declare ranges as Excel ranges
Option Explicit
Private Sub UserForm_Initialize()
Dim objExcel As New Excel.Application
Dim ArticleList As Excel.Workbook
Dim FileName As String
Dim r1, r2, r3, r4, r5, r6, r7, r8, r9 As Excel.Range
Dim myMultiAreaRange As Excel.Range
Dim DataRange As Excel.Range
Dim ClipBoard As DataObject
Application.ScreenUpdating = False

FileName = "S:\Etimex Forms\Worksheet in Basis (1).xls" ' set the file string

Set ArticleList = objExcel.Workbooks.Open(FileName) ' open the file
Sheets.Add.Name = "Sheet2" ' add sheet 2
Worksheets("Sheet1").Activate ' activate sheet 1 and set ranges
Set r1 = Range("A1:A1500")
Set r2 = Range("D1:1500")
Set r3 = Range("E1:E1500")
Set r4 = Range("K1:K1500")
Set r5 = Range("R1:R1500")
Set r6 = Range("U1:U1500")
Set r7 = Range("AK1:AK1500")
Set r8 = Range("AO1:AO1500")
Set r9 = Range("AP1:AP1500")

Set myMultiAreaRange = Union(r1, r2, r3, r4, r5, r6, r7, r8, r9)
myMultiAreaRange.Copy ' copy from sheet 1 and then paste in sheet 2
Worksheets("Sheet1").Paste Destination:=Worksheets("Sheet2").Range("A1:I1500")
' Select the data to load into Listbox
Set DataRange = ArticleList.Worksheets("Sheet2").Range("A1").End(xlDown).CurrentRegion

ListBox1.ColumnCount = 9 ' set the number of columns
ListBox1.List = DataRange.Value

Set ClipBoard = New DataObject ' clear the clipboard contents
ClipBoard.SetText ""
ClipBoard.PutInClipboard

Set objExcel = Nothing
Set ArticleList = Nothing
Set myMultiAreaRange = Nothing
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Unload Me
ActiveWorkbook.Close (False)
objExcel.Quit
End Sub