ComboBox1

pescioletto · September 5th, 2011, 11:38 AM

Hi,

I have created a ComboBox in a Userform.

I have added the following items using code below:

Code:

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "France"
        .AddItem "Italy"
        .AddItem "Spain"
        .AddItem "Guatemala"
    End With
End Sub

Instead of adding each item (ie. country names) can I put in code the reference to a country list in Excel? This way I would not have to add each single item but simply reference cells.

So if my country names were found in Sheet1 from A1 to A4 how would my code look?

Thanks!

GeneBuchite · September 6th, 2011, 07:07 AM

The VB end of tis is quite simple...

Code:

 

Public Class Form1
 

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        FillCBO()
    End Sub


    Public Sub FillCBO()
       
        ' Put your code here to read from xl... I'm not sure how but if I were doiing it I would   export the XLS information From the Spreadsheet
        ' into a Text file named "c:\TextFiles\Countries.
           ' Then Read the text file with a stream reader ie.
        Dim fileReader As System.IO.StreamReader
        fileReader = _
        My.Computer.FileSystem.OpenTextFileReader("C:\TextFiles\Countries.txt")


        For j = 1 To 5
              ' While reading the file ...

            ComboBox1.Items.Add(fileReader.ReadLine())

        Next


        ' search "Help" For "How to read Text From Files With A Stream Reader (Visual Basic)"


    End Sub

End Class

Hopefully someone; who is better with VBA than I am; will be able to fill in the gaps for you.... you might search "Help" for How to read from XML files in Visual Basic ... I Think the newer Spreadsheets will allow exporting into a Spread Sheed.... Good Luck!

Shasur · September 7th, 2011, 07:52 AM

Hi

You can link the Listbox to an Excel table using VBA (http://vbadud.blogspot.com/2011/09/h...o-listbox.html)

Cheers
Shasur