Where to go from here?

gmayor · #1 03-27-2016, 06:12 AM

There's a thread at http://answers.microsoft.com/en-us/o...6-8db862b6032c which demonstrates how to read an Access table into a list box, and a similar code example for Excel on my web site at http://www.gmayor.com/Userform_ComboBox.html

highrise955 · #2 03-27-2016, 02:16 PM

Quote:

Originally Posted by gmayor

There's a thread at Store Outlook email files via VBA - Microsoft Community which demonstrates how to read an Access table into a list box, and a similar code example for Excel on my web site at Populate Userform Combo Box

Quote:

Originally Posted by macropod

See also:

…

Saving with pre-defined names & read-only password protection is managed via the SaveAs2 method.

Quote:

Originally Posted by gmaxey

Regarding #2. Often a document content control itself can serve as a pseudo external source for populating a userform listbox.

I'm ashamed to admit that I went through all three of your replies in exhaustive detail but I still don't seem to be getting it. For now I am just trying to use Excel as the data source. I tried to create a simple form with one list box control and a named range in excel with four values in the column.

Code:

Sub InitializeDocument()
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NoOfRecords As Long
 
    ' Open the database
    'Set db = OpenDatabase("C:\Temp\ItemSheet.xlsx", False, False, "Excel 12.0")
    Set db = OpenDatabase("C:\Temp\ItemSheet.xlsx", False, False, "Excel 12.0; IMEX=1;")
    ' Retrieve the recordset
    Set rs = db.OpenRecordset("SELECT * FROM ItemRange")
 
    ' Determine the number of retrieved records
    With rs
         .MoveLast
         NoOfRecords = .RecordCount
         .MoveFirst
    End With
 
    ' Set the number of Columns = number of Fields in recordset
    ListBox1.ColumnCount = rs.Fields.Count
 
    ' Load the ListBox with the retrieved records
    ListBox1.Column = rs.GetRows(NoOfRecords)
 
    ' Cleanup
    rs.Close
    db.Close
 
    Set rs = Nothing
    Set db = Nothing
 
End Sub

when I load the document nothing happens. No error or anything and the list box doesn't change at all. I thought maybe that "ListBox1" in the code is not right but I tried changing it to the Title of my list box and nothing happened.

Sorry for the never ending newbie questions.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode