Drop-down list with Directories

Hi All,

I was wondering if anyone here could help me out with the following, I have little to none experience with VBA. Well, I can do some very basic stuff.

I would like to have a drop down list in a predefined cell, for example A5. I want the future users of my excel document to be able to click on this drop down list, where a couple of folders will appear, and they can select one folder they need. These folders (no subfolders), for example located in "D:\testomgeving\", will at the moment lead to 4 results. I need these 4 results/folders in the drop-down list.

I'm sorry for using "I" all the time :-).

With kind regards,
Peter de Jong

Use Data Validation's List. Select a range A1:A4 and enter a name, e.g. Ken. In the Source for the list put =Ken.

Dear Kenneth,

Thanks for your reply, but I dont think this is the answer I'm looking for.

The data's validation list give the possibility for entering text. My goal is to have a drop doen list, consisting of all the directory names in a certain directory. This means that when a new directory has been added, for example "D:\testomgeving\project a", the drop down list will also contain the option "project a".

Or I dont understand your answer.

Is your list of directory names automatically generated? or manually typed in every time a new name is added? The data validation can disallow text input and will pop up a message box containing an error if input that doesn't match a directory name is typed.

I'll give a more detailed example:

A location contains certain folders/directories, this location is:
D:\Standard\

At the moment it has 4 subfolders located in the above location, e.g.
D:\Standard\apac
D:\Standard\quality
D:\Standard\offers
D:\Standard\custumers

In the future this list of 4 could expand to 10 or 20 folders, depending on the company changes. But the thing is, I want the drop down list to look for the actual folders at that time. So I was thinking of using VBA ffunctions as xLvalidate and Dir() and Filesystem.

In that Case, put this in a Module after you set the FSO reference:

Sub Test_FoldersInParent()
  MsgBox FoldersInParent(ThisWorkbook.Path)
End Sub

Function FoldersInParent(sParent As String) As String
Rem Needs Reference: MicroSoft Scripting Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
  Dim FSO As New FileSystemObject
  Dim fd As Folder
  Dim s As String
  s = vbNullString
  With FSO
    If Not (.FolderExists(sParent)) Then Exit Function
    For Each fd In .GetFolder(sParent).SubFolders
      s = s & fd.Name & vbLf
    Next fd
  End With
  s = Left(s, Len(s) - 1)
  FoldersInParent = s
End Function

And put this in your sheet object. Change A1 (dropdown data validation cell) and ThisWorkbook.Path (parent folder) to suit.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim s As String
  With Target
    If .Count <> 1 Then Exit Sub
    'Change A1 to suit
    If .Address <> "$A$1" Then Exit Sub
    On Error Resume Next
    With .Validation
      .Delete
    'Change ThisWorkbook.Path to suit for Parent Folder.
      s = Replace(FoldersInParent(ThisWorkbook.Path), vbLf, ",")
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:=s
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
    End With
  End With
End Sub

Hi Kenneth,

Or maybe someone else who is still reading this.

This was of great help, but somehow the results mentioned in the dropdown-list are not alphabetically sorted. This would be a big wish for me, because it will make things a lot clearer.

With kind regards,
Peter

Thanks Kenneth, I will give it a try today!

Use Split() and Join() for arrays and strings. Put the string from the function that I posted into an array. A simple bubble sort should then suffice. Convert the array back into a string with Join().

Here is but one example sort routine. I know of many others.
http://social.msdn.microsoft.com/For...excel-function

All of my tests sorted my subfolders using my first method. So, I am not sure how much better this will be. Maybe you have Window's Explorer set to sort in an order different than mine.

I used a vb.net list sorting method here. Windows 8 has v4.5 vb.net framework files installed already. If this does not work then you probably have an old version of Windows with old vb.net framework files without this method.

I test code before I post so I posted some simple test routines as well. I also showed how to sort either a string or a variant array.

As before, in an Module:

Option Explicit

Sub Test_vArraySort()
  Dim a() As Variant
  a() = Array("b", "c", "a")
  MsgBox Join(vArraySort(a), vbLf)
End Sub

Sub Test_sArraySort()
  Dim a(2) As String
  a(0) = "b"
  a(1) = "a"
  a(2) = "c"
  MsgBox Join(sArraySort(a), vbLf)
End Sub

' http://msdn.microsoft.com/en-us/library/system.collections.arraylist.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
Function sArraySort(anArray() As String) As Variant
  Dim myAL As Object, myALSorted As Object
  Dim element As Variant, a() As Variant
  Set myAL = CreateObject("System.Collections.ArrayList")
  With myAL
    For Each element In anArray()
         .Add (element)
    Next element
    .Sort
    sArraySort = .ToArray()
  End With
End Function

Function vArraySort(anArray() As Variant) As Variant
  Dim myAL As Object, myALSorted As Object
  Dim element As Variant, a() As Variant
  Set myAL = CreateObject("System.Collections.ArrayList")
  With myAL
    For Each element In anArray()
         .Add (element)
    Next element
    .Sort
    vArraySort = .ToArray()
  End With
End Function


Sub Test_FoldersInParent()
  MsgBox FoldersInParent(ThisWorkbook.Path)
End Sub

Function FoldersInParent(sParent As String) As String
Rem Needs Reference: MicroSoft Scripting Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
  Dim FSO As New FileSystemObject
  Dim fd As Folder
  Dim s As String
  s = vbNullString
  With FSO
    If Not (.FolderExists(sParent)) Then Exit Function
    For Each fd In .GetFolder(sParent).SubFolders
      s = s & fd.Name & vbLf
    Next fd
  End With
  s = Left(s, Len(s) - 1)
  FoldersInParent = s
End Function

In a sheet's code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim s As String, sa() As String, a() As Variant, v As Variant
  With Target
    If .Count <> 1 Then Exit Sub
    'Change A1 to suit
    If .Address <> "$A$1" Then Exit Sub
    On Error Resume Next
    With .Validation
      .Delete
      'Change ThisWorkbook.Path to suit for Parent Folder.
      s = FoldersInParent(ThisWorkbook.Path)
      sa() = Split(s, vbLf)
      s = Join(sArraySort(sa()), ",")
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:=s
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
    End With
  End With
End Sub

Thanks Kenneth,
You have been of great help!
I must say that when I read all the changes you have made, to just be able to sort alphabetically, it is many steps to just sort.... But that doesn't matter because it works!

I have a lot more to work on in VBA to achieve my goals at work (I'm HVAC engineer/sales). The goals is to make the quotation process less time consuming. So I can keep asking around on the internet to achieve this goal (some more steps have to be taken), but I also want to become more experienced with VBA. I consider this is a great possibility to enrich my career.

I'm reading a Dutch book which describes a lot of things like: Sub, With, Loops, etcetera. But the possibilities of VBA seem limitless to me at this moment and I wonder how to ever fully comprehend and automatically (without continuously correcting and searching internet) write codes.
So:
1) What is the best way to learn VBA? (Studying and books or problem based learning and asking/looking on internet)
2) How much time would it cost to be able to master programming VBA, apart from the creative processes you can keep on desiging and designing.

Kind regards,
Peter

1. Depends, what works best for you?
2. A lifetime.

To get started, try this:
http://dmcritchie.mvps.org/excel/getstarted.htm

Other MVP's like Chip Pearson have excellent resources.
http://www.cpearson.com/excel/MainPage.aspx

If you like books, John Walkenbach has some good ones.
http://spreadsheetpage.com/

Best way to learn is to teach. Try looking at posts on forums like this and try to solve the problem. There is usually more than one way to solve a problem. Remember, simple code is not always best. Code that is robust will trap known errors. Many solutions can be found by key word searches.