Select value in combobox display wksheet name in listbox

[Yvannia]

i have 2 comboboxes, 
comboYEAR to hold the years from 2005 to current year 
comboMonth to hold the 12 months jan thru dec 
a listbox to display results named -displaylist 
All my worksheets are named in this fashion - dd-mmm-yyyy (eg. 23-jan-2008) 

my user is allowed to select from both combos 
When he selects the year combo, all the sheets the particular yyyy is displayed in the listbox 
when he selects the month combo, the search is refined and only that particular month in the year is displayed 
when he clicks the items displayed in the listbox,the particular worksheet opens up inthe background. 

PROBLEM: 
the year combo works fine but my month combo is not working well... 
when I select a month in the combobox, instead of displayong sheetnames with just the selected months, all the sheets are displayed :(

can anyone please advise? 
Any help at all will be greatly appreciated 



here are my codes: 


[code]
Code:


Option Explicit 

Private Sub ComboMONTH_Change() 
Dim ws As Worksheet 

    With ComboMONTH 
       If .ListIndex <> -1 Then 
            DisplayList.Clear 
            For Each ws In Worksheets 
                If IsDate(ws.Name) Then 
                If IsDate(ComboMONTH.Value) = Val(.Value) Then 
                        DisplayList.AddItem ws.Name 
                    End If 
                End If 
            Next ws 
        End If 
    End With 


End Sub 

Private Sub ComboYEAR_Change() 

Dim ws As Worksheet 

    With ComboYEAR 
        If .ListIndex <> -1 Then 
            DisplayList.Clear 
            For Each ws In Worksheets 
                If IsDate(ws.Name) Then 
                    If Year(DateValue(ws.Name)) = Val(.Value) Then 
                        DisplayList.AddItem ws.Name 
                    End If 
                End If 
            Next ws 
        End If 
    End With 

End Sub 

Private Sub DisplayList_Click() 
Dim ws As Worksheet, wb As Workbook 

Set wb = ActiveWorkbook 

For Each ws In wb.Worksheets 
ComboYEAR.AddItem ws.Name 
Next 
 'When Search button is clicked' 
 Dim I As Integer, sht As String 
  For I = 0 To DisplayList.ListCount - 1 'to go thru all the worksheets in the workbook starting with the first and adding the name of the worksheet to the listbox' 
   If DisplayList.Selected(I) = True Then 'if the particular worksheetname is selected, the activate/open the requested worksheet' 
     sht = DisplayList.List(I) 
       End If 
    Next I 
   Sheets(sht).Activate 'open the requested worksheet' 
   Unload Me 
    SEARCH_DATE.Show 
     
  End 

End Sub 


Private Sub UserForm_Activate() 

Dim I As Long 
Dim lMonth As Long 

    For lMonth = 1 To 12 
       ComboMONTH.AddItem Format(DateSerial(2000, lMonth, 1), "MMM") 
  Next lMonth 
     
    For I = 2005 To Year(Date) 
        ComboYEAR.AddItem I 
    Next I 
End Sub 




[/code]

 

[strongm]

>IsDate(ComboMONTH.Value) = Val(.Value)

IsDate of ComboMONTH in your program will always return False. As will Val(.Value).

So the comparison shown above always returns True ...

You'd want

Month(DateValue(ws.Name)) = .ListIndex + 1

But this only partially fixes your problem - which is that you are actually filtering on Year and Month in isolation. So if you select Year, you'll get all the year sheets in DisplayList (no matter what month), and if you select Month you'll get all the matching month sheets no matter what year they are in. So you actually need to consider a slight redesign ...