Populate combobox with folder names

D

damorrison

I had found this code to show folder names that displays in a msgbox,
is it possible to get the list of folder names into a combobox?

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

Sub ListFolders()
Dim fs, f, f1, fc, s
Dim folderspec
folderspec = "C:\Excel\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc
s = s & f1.Name
s = s & vbCrLf

Next
MsgBox s


End Sub
 
D

Dave Peterson

Option Explicit
Sub ListFolders()
Dim fs, f, f1, fc, s
Dim folderspec
folderspec = "C:\Excel\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc
Worksheets("Sheet1").ComboBox1.AddItem f1.Name
Next f1
End Sub

Change the name of the worksheet and the name of the combobox if you have to.
 
D

damorrison

Thank you, that works great

The code works well.

When I use it on a system that uses two screens, and click the
ComboBox, the list shows up on the opposite screen as the combobox. I
then used a UserForm instead to populate the combobox, placing the
code in UserForm_Initialize, it populated just fine, but then again,
when I click the combobox in the UserForm, the list still shows up in
the opposite screen instead of the screen with the userform, is there
a way to stop this from happening?
 
D

Dave Peterson

I don't understand.

The code puts the list in a combobox on Sheet1. I don't see anything that uses
a userform.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top