Populating Combo Boxes Still Not Working

G

Guest

For some reason I still cannot get the combo boxes to populate using the code
below. Does anyone have any other recommendations on how I could get this to
work? Thank you to the gentleman who helped me earlier with this but it's
still not working. Any help would be greatly appreciated !


Private Sub Populate_cboSOperations()

'Populate Combo Box cboSOperations

Dim FSO As Object
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object
'Dim This As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set This = ActiveWorkbook
Folder = "C:\Ag Valley\Tracker"

If Folder <> "" Then
Set sFolder = FSO.GetFolder(Folder)
Set Files = sFolder.SubFolders
cboSOperation.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSOperation.AddItem file.Name
End If
Next file
End If

Set FSO = Nothing
End Sub

Private Sub cboSOperation_Change()

'Populate Combo Box cboSGrower

Dim FSO1 As Object
Dim s1Folder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object
'Dim This As Object

Set FSO1 = CreateObject("Scripting.FileSystemObject")
Set This = ActiveWorkbook
Folder = "C:\Ag Valley\Tracker" & Trim(cboSOperation.Value) & "\"

If cboSOperation.Value <> "" Then
Set s1Folder = FSO1.GetFolder(Folder)
Set Files = s1Folder.SubFolders
cboSGrower.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSGrower.AddItem file.Name
End If
Next file
End If

Set FSO1 = Nothing
End Sub

Private Sub cboSGrower_Change()

'Populate Combo Box cboSYear

Dim FSO2 As Object
Dim s2Folder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object
'Dim This As Object

Set FSO2 = CreateObject("Scripting.FileSystemObject")
Set This = ActiveWorkbook
Folder = "C:\Ag Valley\Tracker" & Trim(cboSOperation.Value) & "\" &
Trim(cboSGrower.Value) & "\"

If cboSGrower.Value <> "" Then
Set s2Folder = FSO2.GetFolder(Folder)
Set Files = s2Folder.SubFolders
cboSYear.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSYear.AddItem file.Name
End If
Next file
End If

Set FSO2 = Nothing
End Sub

Private Sub cboSYear_Change()

'Populate Combo Box cboSFile

Dim FSO3 As Object
Dim s3Folder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object
'Dim This As Object

Set FSO3 = CreateObject("Scripting.FileSystemObject")
Set This = ActiveWorkbook
Folder = "C:\Ag Valley\Tracker" & Trim(cboSOperation.Value) & "\" &
Trim(cboSGrower.Value) & "\" & Trim(cboSYear.Value) & "\"

If cboSYear.Value <> "" Then
Set s3Folder = FSO3.GetFolder(Folder)
Set Files = s3Folder.SubFolders
cboSFile.Clear
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
cboSFile.AddItem file.Name
End If
Next file
End If

Set FSO3 = Nothing
End Sub
 
N

Norman Jones

Hi Tyrell,

You appear to confuse the Folder and sFolder variables.

Try replacing your first sub with:

'===========>>
Private Sub Populate_cboSOperations()

'Populate Combo Box cboSOperations

Dim FSO As Object
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\Ag Valley\Tracker"

If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.SubFolders
cboSOperation.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSOperation.AddItem file.Name
End If
Next file
End If

Set FSO = Nothing
End Sub

'<<========

Make analogous amendments to the other procedures.
 
G

Guest

Tom,
The combo box simply would not fill. But it appears it was a mistake I made
when I used your recommended code. I can fill the first combo box but the
second combo box will not fill based on what is selected in the first. Any
recommendations?
Regards,


Private Sub Populate_cboSOperations()

'Populate Combo Box cboSOperations

Dim FSO As Object
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\Ag Valley\Tracker"

If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.SubFolders
cboSOperation.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSOperation.AddItem file.Name
End If
Next file
End If

Set FSO = Nothing
End Sub


Private Sub cboSOperation_Change()

Dim FSO As Object
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\Ag Valley\Tracker" & Trim(cboSOperation.Value) & "\"

'If cboSOperation.Value <> "" Then
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.SubFolders
cboSGrower.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSGrower.AddItem file.Name
End If
Next file
End If

Set FSO = Nothing
End Sub
--
Tyrell Fickenscher
Plant Manager / Agronomist


Tom Ogilvy said:
Which one isn't working?
What is the error.

Which line is highlighted.
Click to expand...
 
K

kounoike

Though I don't know how you make comboboxes, my guess about why second
combo box is not filled is cboSOperation_Change would be not fired.
because if it was fired, Sub cboSOperation_Change would end up with
error at Set Folder = FSO.GetFolder(sFolder) in my thought.
i think Trim(cboSOperation.Value) is just folder name without path
separator, so sFolder = "C:\Ag Valley\Tracker" &
Trim(cboSOperation.Value) & "\" would be wrong path.

keizi

Tyrell said:
Tom,
The combo box simply would not fill. But it appears it was a mistake I made
when I used your recommended code. I can fill the first combo box but the
second combo box will not fill based on what is selected in the first. Any
recommendations?
Regards,


Private Sub Populate_cboSOperations()

'Populate Combo Box cboSOperations

Dim FSO As Object
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\Ag Valley\Tracker"

If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.SubFolders
cboSOperation.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSOperation.AddItem file.Name
End If
Next file
End If

Set FSO = Nothing
End Sub


Private Sub cboSOperation_Change()

Dim FSO As Object
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\Ag Valley\Tracker" & Trim(cboSOperation.Value) & "\"

'If cboSOperation.Value <> "" Then
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.SubFolders
cboSGrower.Clear
For Each file In Files
If file.Type = "File Folder" Then
cboSGrower.AddItem file.Name
End If
Next file
End If

Set FSO = Nothing
End Sub
Click to expand...
 

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

Similar Threads

Populating Combo Boxes 1
prompt for path to loop through files 5
Print contents of sub folders 1
Object varaible or With block variable not set? 6
I need a vba code to download attachments from Outlook which are sent daily 1
Excel Populate workbook A from Data in workbook B based off criteria in Workbook C 0
Import multiple workbooks to current workbook in specified row number 0
maybe to bob phillips -next folder? 2

Top