Combo box in excel using outside data 2

[VE]

Can you make a combo box, or validation criteria in Excel using an outside excel file (that is updated several times daily) or a MS access table for the combo box data? I think I could also put it in a .txt if I have to. It just can't be in the excel file because it's very large and there will be multiple copies of the excel file.

I want to create a combo box that will allow the user to select a record from this outside file to populate a cell and not allow them to add any data that is not in that file. Much like a combo box in an Access form, only this has to be in Excel.

The data would not be stored in the same file as the combo box. Is that possible?

Thank you

 

[cLFlaVA]

VE

Here's a Sub that does what you want, using a text file.

Public Sub FillCombo()
    Dim fName As String
    Dim lineVal As String
    fName = "C:\lookup.txt"
    
    ' remove existing entries in combobox
    With Sheet1.ComboBox1
        While .ListCount > 0
            .RemoveItem (0)
        Wend
    End With
    
    'open text file for reference
    Open fName For Input As #1
    
    'fill combobox
    Do While Not EOF(1)
        Line Input #1, lineVal
        Sheet1.ComboBox1.AddItem (lineVal)
    Loop
    
    'close file
    Close #1
End Sub

Assumptions in the code:
- Your ComboBox is named "ComboBox1"
- Your ComboBox resides on the sheet "Sheet1"
- Your reference file is named lookup.txt and is in C:\
- Your reference file looks like:

value 1
value 2
value 3
value 4

Let me know if you need any additional pointers.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[SkipVought]

Hi,

If your external source is a database, you can use MS Query (built-in) via Data/Get External Data/New Query...

You list can refresh on command or on the Workbook_Open event.

You can use the OFFSET Function in Insert/Names/Define to define a DYNAMIC range and use the corresponding Range Name in the ListFillRange property of the ComboBox, so your list will ALWAYS reflect the entire list range.

See: How can I rename a table as it changes size faq68-1331 to see how the OFFSET function can create a DYNAMIC NAMED range.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[VE]

Thank you