#1
|
|||
|
|||
Data from Excel to Listbox multiselect to Word
Hello, fellow members,
I'm a bit stuck. I'm pulling data for a multiselect listbox in Word from a range in Excel. The range is referred to as "Rub1a" The Excel file's name is "TE1.xltm". The listbox's name is "lb1a". The userform's name is UF1a. When I run the code below (big thanks to Greg Maxey for helping me get this far), I get the "Run-time Error 1004 App-defined or object Defined Error." The range only has one column in it and it is static. When I debug the code, the error pops up on the columncount line, which is set = 1. I've tried to use the same code with a different non-macro Excel file, to see if that was the problem, but the same error occurs. I've checked the spellings of all the controls, files and ranges and they are accurate. I'm using a late binding method. Any ideas about where I went wrong? I feel like it's staring me right in the face and I'm just missing it Could it be with something from my Excel settings? I'm using Word and Excel 2010 on a Windows 7 machine. Thanks, Mark Code:
Private Sub Userform_Initialize() 'Late binding. No reference to Excel Object required. Dim xlApp As Object Dim xlWB As Object Dim xlWS As Object Dim cRows As Long Dim i As Long Set xlApp = CreateObject("Excel.Application") 'Open the spreadsheet to get data Set xlWB = xlApp.Workbooks.Open("C:\Users\mark.miller\Desktop \TE1.xls") Set xlWS = xlWB.Worksheets(2) cRows = xlWS.Range("Rub1a").Rows.Count - xlWS.Range("Rub1a").Row + 1 LB1a.ColumnCount = 1 'Populate the listbox. With Me.LB1a For i = 2 To cRows 'Use .AddItem property to add a New row For Each record And populate column 0 .AddItem xlWS.Range("Rub1a").Cells(i, 1) 'Use .List method to populate the remaining Columns .List(.ListCount - 1, 1) = xlWS.Range("Rub1a").Cells(i, 2) .List(.ListCount - 1, 2) = xlWS.Range("Rub1a").Cells(i, 3) Next i End With 'Clean up Set xlWS = Nothing Set xlWB = Nothing xlApp.Quit Set xlApp = Nothing lbl_Exit: Exit Sub End Sub Private Sub cb1a_Click() For i = 0 To lb1.ListCount - 1 If lb1.Selected(i) = True Then strDocs = strDocs & lb1.List(i) & vbCr End If Next i strDocs = Left$(strDocs, Len(strDocs) - 1) ActiveDocument.Bookmarks("bm1a").Range _ .InsertBefore strDocs UserForm1.Hide End Sub Last edited by macropod; 02-16-2014 at 04:14 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
It's not apparent to me why you'd get that error, though you might do better with:
Me.LB1a.ColumnCount = 1 PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks!
That did make a difference in the code. I also found out, however, that I needed to insert reference to MS objects.
Thanks for the tips and reply! Mark |
Tags |
listbox, multiselect, runtime error 1004 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Moving Selected Items from a Multiselect Listbox on a userform to a bookmark in Word | marksm33 | Word VBA | 3 | 01-15-2015 07:55 PM |
Getting mult resps. selected in listbox to concatenate & paste at a bookmark in Word | marksm33 | Word VBA | 3 | 01-15-2015 05:59 PM |
How to use an ActiveX Control to insert a multi select listbox in Word | marksm33 | Word | 2 | 01-29-2014 05:21 PM |
data from Excel into Word table | ragesz | Word Tables | 1 | 09-29-2013 06:14 PM |
word insert data into excel | elemental | Word | 1 | 04-29-2011 06:33 PM |