Excel 2007 sort combobox

**LaurieH** · 05-19-2013, 07:06 AM

Hi everyone, this is my first post, and I am NOT a proficient VBA programmer. For some of you this might be easy, but after 2 days, it is driving me nuts.

I have a combobox which is populated with folder names,, the folder names are month and year. eg Apr 2013, this works fine, no problems. The problem is, in the combobox list the months are in alphabetical order, I would like to sort them so that they run from Jan 2013 to Dec 2013.

All help appreciated, but PLEASE keep it relevant.

My code:

Private Sub CommandButton1_Click()
Dim name

Me.ComboBox2.Clear
For Each name In ListDirectory(Path:="C:\Users\Laurie\Documents\Pai Hotel\Log\Daily Log - Year 2013\", AttrInclude:=vbDirectory, AttrExclude:=vbSystem Or vbHidden)
If name Like "Jan*" Or name Like "Feb*" Or name Like "Mar*" Or name Like "Apr*" Or name Like "May*" Or name Like "Jun*" _
Or name Like "Jul*" Or name Like "Aug*" Or name Like "Sep*" Or name Like "Oct*" Or name Like "Nov*" Or name Like "Dec*" Then

Me.ComboBox2.AddItem name
End If
Next name

End Sub

**nilem** · 05-19-2013, 08:25 AM

Hi LaurieH,
maybe something like this

Private Sub CommandButton1_Click()
Dim arr, i As Long, nm
arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
For Each nm In ListDirectory(Path:="C:\Users\Laurie\Documents\Pai Hotel\Log\Daily Log - Year 2013\", AttrInclude:=vbDirectory, AttrExclude:=vbSystem Or vbHidden) '??
    For i = LBound(arr) To UBound(arr)
        If InStr(nm, arr(i)) Then arr(i) = nm: Exit For
    Next
Next nm
Me.ComboBox2.Clear
For Each nm In arr
    If Len(nm) > 3 Then Me.ComboBox2.AddItem nm
Next nm
End Sub

**LaurieH** · 05-20-2013, 05:08 AM

nlerm, thanks for your very quick reply, your code is exactly what I needed, I have a lot to learn

.

**snb** · 05-19-2013, 08:54 AM

You can fill the comobox with montnames:

sub M_snb()
  combobox1.list=Application.GetCustomListContents(3)
end sub

After selection you can open the file using:

Private Sub Combobox1_change()
  If combobox1.listindex<>-1 then workbooks.open "C:\Users\Laurie\Documents\Pai Hotel\Log\Daily Log - Year 2013\" & dir("C:\Users\Laurie\Documents\Pai Hotel\Log\Daily Log - Year 2013\*" & combobox1.value & "*")
End Sub