PDA

View Full Version : Solved: Create listbox via vba: populating listfillrange



fboehlandt
09-17-2008, 04:16 AM
Hello everyone,
I want to create a listbox from which a user must select an entry before the source code continues to run. The listfillrange should consist of two filelists (filelist hardrive C:\ and filelist harddrive D:\). The two filelists are composed via vba and dimensioned as Variant. All files within the two filelists should now make up the listfillrange. The steps are:
- Create a listbox
- populate listfillrange with files from the two filelists
- Activate listbox to inquire selection by user
- resume code with selected file
Can anyone help please? Many thanks in advance
Florian

Kenneth Hobs
09-17-2008, 06:07 AM
I would assume that you mean all files in drives C and D? If that is so, the list would be way too large. In that case, it is better to just let the user pick the file from an Application.GetOpenFilename.

fboehlandt
09-17-2008, 06:20 AM
No, not all files. The code searches for files on the two harddrives matching certain criteria. This bit of coding is up and running. There will never be more than 4-5 files to choose from (where the name is sufficiently identical to the search specifications). The user must now select the appropriate one from those files. Any ideas?

Kenneth Hobs
09-17-2008, 08:05 AM
Do you mean a listbox on a userform?

fboehlandt
09-17-2008, 08:59 AM
The user 'interface' would be a spreadsheet. Let's assume the code starts running on a certain action (e.g. opening spreadsheet, pressing command button etc.). The code begins by sifting through harddrives to find files that match the search criteria. From the files the user must then select the appropriate file that will be used for data import in the follow-up code. When the code is executed, a listbox should 'pop-up' in the excel environment to inquire about the correct file, only then must the code be allowed to continue. I realize this is a bit of an unusual request, but maybe the solution to this is not too complicated?!

Kenneth Hobs
09-17-2008, 09:29 AM
Create a Userform and add a ListBox and a Button. Doubleclick the userform and insert something like:

Private Sub CommandButton1_Click()
MsgBox "You picked: " & ListBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim files() As String
ReDim files(1 To 5)
files(1) = "Apple"
files(2) = "Bannana"
files(3) = "Grape"
files(4) = "Orange"
files(5) = "Pear"
ListBox1.List = files()
ListBox1.ListIndex = 0 'Select first entry
End Sub

If you want to show Userform1, then put this code in a control toolbox button's click event:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Obviously, you would want to use your values for files(). Modify the button's click event to replace my example MsgBox with your needs.

fboehlandt
09-18-2008, 04:17 AM
Thanks Kenneth,
as usual you have been a tremendous help. I'll give this a bash,
cheers:thumb

fboehlandt
09-18-2008, 06:22 AM
...and it works nicely!!!