How to load values to a form combo box

[mthomas]

We are using the FileSystemObject to capture folder names in a
specified path and need to list them in a combo box in the spreadsheet. How
do we assign them to the row source of the drop down?

'CODE********************************
Dim fso As New FileSystemObject
Dim flds As Folders
Dim strText As String
Dim i As Integer

Set flds = fso.GetFolder("C:\").SubFolders
i = 1

For Each f In flds
strText = f.Name
'NEED CODE HERE TO LIST FOLDERS IN DROP DOWN ROW SOURCE.
i = i + 1
Next
'END**********************************

 

[Dave Peterson]

Maybe...

For Each f In flds
strText = f.Name
worksheets("Sheet999").combobox1.additem strtext
'i = i + 1
Next f

If you really wanted to use .rowsource, you'll have to populate a worksheet with
those string.

 

[mthomas]

Getting an error saying it doesn't support the object or method. We can
populate a worksheet. So, do you have a syntax example of populating the
..rowsource?
--
Kind Regards,
Mike

Maybe...

For Each f In flds
strText = f.Name
worksheets("Sheet999").combobox1.additem strtext
'i = i + 1
Next f

If you really wanted to use .rowsource, you'll have to populate a worksheet with
those string.

 

[Dave Peterson]

Did you put a combobox from the control toolbox toolbar on the correct
worksheet?

Did you change the code so that it used the correct name for the worksheet and
the correct name for the combobox?

I thought that your combobox was placed on a worksheet--not a userform. Was I
wrong? .Rowsource is used with comboboxes on userforms.

Getting an error saying it doesn't support the object or method. We can
populate a worksheet. So, do you have a syntax example of populating the
.rowsource?

 

[mthomas]

Yes, the combo box was from the control toolbox toolbar and inserted in
Sheet1. When I select the combo box (holding the CTRL key) the name in the
macro name box is, "DropDown1_Change".

So the code I used was:

For Each f In flds
strText = f.Name
Worksheets("Sheet1").DropDown1_Change.AddItem strText
i = i + 1
Next

--
Kind Regards,
Mike

Did you put a combobox from the control toolbox toolbar on the correct
worksheet?

Did you change the code so that it used the correct name for the worksheet and
the correct name for the combobox?

I thought that your combobox was placed on a worksheet--not a userform. Was I
wrong? .Rowsource is used with comboboxes on userforms.

 

[Dave Peterson]

That's a pretty unusual name for a combobox from the control toolbox.

It looks like someone got confused and used a dropdown from the forms toolbar
and then used the suggested macro name (rightclick on a dropdown from that forms
toolbar and select Assign Macro to see it).

Yes, the combo box was from the control toolbox toolbar and inserted in
Sheet1. When I select the combo box (holding the CTRL key) the name in the
macro name box is, "DropDown1_Change".

So the code I used was:

For Each f In flds
strText = f.Name
Worksheets("Sheet1").DropDown1_Change.AddItem strText
i = i + 1
Next

 

[mthomas]

--
Kind Regards,
Mike

That's a pretty unusual name for a combobox from the control toolbox.

It looks like someone got confused and used a dropdown from the forms toolbar
and then used the suggested macro name (rightclick on a dropdown from that forms
toolbar and select Assign Macro to see it).

 

[mthomas]

That exactly right. I'm not use to working with combo boxes in a
spreadsheet. So, after inserting the dropdown from the form's toolbox, how
do I get the name of the control?

 

[mthomas]

Dave, thaks so much for the help. I actually found the answer by using the
following code (extract):

'START***************************
Private Sub ComboBox1_GotFocus()
'Code here the build folder collection...

ComboBox1.Clear
For Each f In flds
strText = f.Name
'Line below lists values in the combo box.
ComboBox1.AddItem strText
i = i + 1
Next

'END *****************************