Populate combobox with folder names

[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

 

[dan dungan]

is the combobox on a worksheet or a userform?

 

[damorrison]

is the combobox on a worksheet or a userform?

Thanks for the reply, it will be from the controlls toolbar that will
be on the worksheet.

 

[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.

 

[damorrison]

Thank you, that works great

 

[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?

 

[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.