Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-15-2014, 01:40 PM
marksm33 marksm33 is offline Data from Excel to Listbox multiselect to Word Windows 7 64bit Data from Excel to Listbox multiselect to Word Office 2013
Novice
Data from Excel to Listbox multiselect to Word
 
Join Date: Jan 2014
Posts: 12
marksm33 is on a distinguished road
Exclamation 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
Reply With Quote
  #2  
Old 02-16-2014, 04:24 PM
macropod's Avatar
macropod macropod is offline Data from Excel to Listbox multiselect to Word Windows 7 32bit Data from Excel to Listbox multiselect to Word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,234
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #3  
Old 02-18-2014, 08:30 AM
marksm33 marksm33 is offline Data from Excel to Listbox multiselect to Word Windows 7 64bit Data from Excel to Listbox multiselect to Word Office 2013
Novice
Data from Excel to Listbox multiselect to Word
 
Join Date: Jan 2014
Posts: 12
marksm33 is on a distinguished road
Default 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
Reply With Quote
Reply

Tags
listbox, multiselect, runtime error 1004

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data from Excel to Listbox multiselect to Word 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
Data from Excel to Listbox multiselect to Word 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 to Listbox multiselect to Word data from Excel into Word table ragesz Word Tables 1 09-29-2013 06:14 PM
Data from Excel to Listbox multiselect to Word word insert data into excel elemental Word 1 04-29-2011 06:33 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:52 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft