Populating Combo Boxes

[Guest]

I am trying to populate combo boxes using the code below but it is not
working for me. Once the workbook is opened I want the first combo box to
populate automatically. From what is selected by the user from the first
combo box I want the second to populate and so on until the Excel file is
selected in the fourth combo box. What am I doing wrong? Any help on this
would be greatly appreciated!


'Populate Combo Boxes
Dim FSO As Object
Dim FSO1 As Object
Dim FSO2 As Object
Dim FSO3 As Object
Dim sFolder As String
Dim s1Folder As String
Dim s2Folder As String
Dim s3Folder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object


'Populate cboSOperation
Set FSO = CreateObject("Scripting.FileSystemObject")
Set this = ActiveWorkbook
Set sFolder = "C:\Ag Valley\Tracker"

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

Set FSO = Nothing

'Find folders to populate cboSGrower
Set FSO1 = CreateObject("Scripting.FileSystemObject")
Set this = ActiveWorkbook
Set s1Folder = this.cboSOperation.Value

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

Set FSO1 = Nothing

'Find folders to populate cboSYear
Set FSO2 = CreateObject("Scripting.FileSystemObject")
Set this = ActiveWorkbook
Set s2Folder = this.cboSGrower.Value

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

Set FSO2 = Nothing

'Find Excel files to populate cboSFile
Set FSO3 = CreateObject("Scripting.FileSystemObject")
Set this = ActiveWorkbook
Set s3Folder = this.cboSYear.Value

If s3Folder <> "" Then
Set Folder = FSO3.GetFolder(s3Folder)
Set Files = Folder.Files
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

 

[Guest]

Perhaps something like this:

Sub Populate_cboSoperations()

'Populate cboSOperation
Set fso = CreateObject("Scripting.FileSystemObject")
Set This = ActiveWorkbook
Folder= "C:\Ag Valley\Tracker" '<=== no SET required

If Folder<> "" Then
Set Folder = fso.GetFolder(Folder)
Set Files = Folder.SubFolders '<=== note change here
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


You will then need separate macros to populate the other combos depending on
selection - code sould be in sheet containing the combos (right click on tab
==> view code) e.g.

Private Sub cboSOperation_Change()
'Find folders to populate cboSGrower
Set FSO1 = CreateObject("Scripting.FileSystemObject")
Set This = ActiveWorkbook
' note folder path will change as you select next level of path
folder = "C:\Ag Valley\Tracker" & Trim(cboSOperation.Value) & "\"

If cboSOperation.Value <> "" Then
Set Folder = FSO1.GetFolder(Folder)
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 FSO1 = Nothing
End Sub

Similar code is required for the other combos.

HTH