Userform, ComboBox and Outside data...

[Guest]

I tried to search, but nothing turned up (could be me! )

I'm putting together a rather large and complex set of userforms/worksheets
for my work and it's an "information from Userform1 leads into Userform2...
leads into Userrform3 (etc)" type of situation. Of course, I'm in a time
crunch so I don't have the option of setting up an Access DB (unfortunately)
and, due to the size of the form, loading all the ComboBoxes (and other
things) using an Initialize routine has started to slow down the startup of
the userform(s). So (hopefully) with minimal confusion, here's the question:

Is it possible to populate a ComboBox with items from an outside text file
depending on the caption of a label?

For example: On Userform1, a user had the choice of planes, trains or
automobiles. Trains was the option selected so the caption for a label on
Userform2 is "Trains". Can a ComboBox then be populated by trains.txt with
itmes like "Locomotive Info", "Boxcar Info", Flatcar Info", "Caboose Info",
etc ?

 

[Tyla]

The short answer is "Yes you can" but it depends on a lot of things.
Note: Excel can open text files directly within a workbook, you just
have to give it the full file name - assuming it's constant and some
hint about delimiters, etc. (Try "Tools / Macro / Record" for a first
cut for the syntax here.). Assuming the text file content has a fixed
format and delimiters -- so you know exactly where the real data
starts (and ends, of course, though VBA can be used for that part) --
the next step is jsut to iterate through the text data on the
worksheet to populate the combobox. Code like the following can be
used here. It makes a lot of simplifying assumptions, but it should
point you in a useful direction.
The only other element you'll need here to associate the content of
the label on your form to a particular text file. The contents of the
label can be read in VBA using code like
"frmMyFavoriteForm.lblMyFavoriteLabel.Caption"

HTH

/ Tyla /

---------------------------------

Sub fillMyBox()

Dim d As Double
Dim rngDataBL As Range

' Init
Set rngDataBL = Worksheets("TextFileData").Range("A1")
dRows = rngDataBL.CurrentRegion.Rows.Count

With frmMyFavoriteForm
' Out with the old data
.boxTransportModes.Clear

' In with the new data
For d = 1 To dRows
.boxTransportModes.AddItem = Trim(rngDataBL (d, 0).Value)
Next d

End With

Set rngDataBL = Nothing

End Sub