MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fill Combo with files from folder


Posted by Andonny on July 14, 2001 6:34 PM

Hi,
I have a combobox on a form and I would like to fill it with files from a specific folder (c:\My Documents\.....). The reason for this is that people can select only one of these files in that folder and not being able to go anywhere else. When they click on one file and hit a cmd button the workbook selected should load.

Thank you very much for your help
Andonny


Posted by Ivan F Moala on July 14, 2001 7:39 PM

Try this;

Private Sub UserForm_Initialize()
Dim F

F = Dir("C:\My Documents\*.xls")

Do While Len(F) > 0
ComFiles.AddItem F
F = Dir()
Loop

End Sub

Note:
1) No error checks,
2) assums ComboBox name = comFiles


Ivan

Posted by Andonny on July 14, 2001 9:26 PM

Thanks a million Ivan. works perfectly.

Now I just need to work out how to load the xls workbook selected.

Andonny

Posted by Ivan F Moala on July 15, 2001 3:24 AM

Private Sub CommandButton1_Click()
On Error GoTo ErrOpen
Workbooks.Open Filename:="C:\My Documents\" & ComFiles.Value
Exit Sub
ErrOpen:
MsgBox Err.Number & ":=" & Err.Description, _
vbMsgBoxHelpButton, _
"Error Open", _
Err.HelpFile, _
Err.HelpContext

End Sub