AlvinChaand
09-19-2009, 01:30 PM
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:
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
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:
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