Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating Combo Boxes
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 -- Tyrell Fickenscher Plant Manager / Agronomist |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating Combo Boxes
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 "Tyrell" wrote: 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 -- Tyrell Fickenscher Plant Manager / Agronomist |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes | Excel Programming | |||
Populating combo boxes | Excel Programming | |||
Populating Combo Boxes | Excel Programming |