Selecting Quarter 1, Year 2014 Dates in ListBox

Hello,

I have a ListBox1 that is populated with Dates.

I want to be able to assign a macro to a CommandButton that selects Quarter 1 dates for 2013. I wrote something like this:

Private Sub CommandButton1_Click()
For I = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(I) = Year(ListBox1.List(I)) = 2013 Then
        ListBox1.Selected(I) = Month(ListBox1.List(I)) = 1
        ListBox1.Selected(I) = Month(ListBox1.List(I)) = 2
        ListBox1.Selected(I) = Month(ListBox1.List(I)) = 3
    End If
Next I
End Sub

It doesn't error, it just doesn't work. The code should select all values in the ListBox1 1/1/2013-3/31/2013

I don't understand how to select a YEAR & MONTH to run through a loop and select.

Hello EnigmaMatter,

There may be many reasons why this code is failing. To get an answer to this problem quickly, you should post a copy of your workbook.

Edit: Workbook attached in first post.

Hello EnigmaMatter,

The button's caption says "Select A1/1/2013-3/31/2013" and now you are saying you want the button to select these cells??? Can you explain step by step what the user is to do on this worksheet?

Hello EnigmaMatter,

The statement ListBox1.Selected(I) is a boolean value that tells you if the ListBox item was selected. Where do you want the Year, Month, and Day to go?

Hello

This macro will compare the dates in the user has selected in column "A" and set the item in the ListBox if the dates match.

Private Sub CommandButton1_Click()

    Dim Dates   As Variant
    Dim I       As Long
    Dim Item    As Variant
    Dim LB      As MSForms.ListBox
    Dim Rng     As Range
    
        Set Rng = Selection
        If Rng.Column <> 1 Then Exit Sub
        If Rng.Cells.Count = 0 Then Exit Sub
        
        Set LB = OLEObjects("ListBox1").Object
        
        Dates = Rng.Value
        
        For I = 0 To LB.ListCount - 1
            For Each Item In Dates
                If CDate(LB.List(I)) = Item Then
                    LB.Selected(I) = True
                    Exit For
                End If
            Next Item
        Next I
    
End Sub

Hello EnigmaMatter,

I just made a change to prevent an error if the user selects only a single cell. This version will prevent that from happening.

Private Sub CommandButton1_Click()

    Dim Dates   As Variant
    Dim I       As Long
    Dim Item    As Variant
    Dim LB      As MSForms.ListBox
    Dim Rng     As Range
    
        Set Rng = Selection
        If Rng.Column <> 1 Then Exit Sub
        
        Set LB = OLEObjects("ListBox1").Object
        
        Dates = Rng.Value
        If Not IsArray(Dates) Then
            ReDim Dates(0)
            Dates(0) = Rng.Value
        End If
        
        For I = 0 To LB.ListCount - 1
            For Each Item In Dates
                If CDate(LB.List(I)) = Item Then
                    LB.Selected(I) = True
                    Exit For
                End If
            Next Item
        Next I
    
End Sub

This will select the first quarter in 2013.

Private Sub CommandButton1_Click()
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = ListBox1.List(i) >= DateSerial(2013, 1, 1) And ListBox1.List(i) <= DateSerial(2013, 3, 31)
    Next i
End Sub

Hello All,

Thanks for the replies.

I am trying Norie's solution...but when I adapt for my UserForm, it doesn't quite work.

If CustomerLookupUserForm.ComboBox1.Value = "Quarter 1 (2014)" Then
For I = 0 To Me.DateBox.ListCount - 1
      CustomerLookupUserForm.DateBox.Selected(I) = CustomerLookupUserForm.DateBox.List(I) >= DateSerial(2014, 1, 1) And CustomerLookupUserForm.DateBox.List(I) <= DateSerial(2014, 3, 31)
Next I
End If

I apologize for the multiple edits.

The code above does in fact not work, though it does not error.

How does it not work?

Can you upload the file with the userform?

What is Listbox1 in this code?

Izandol

Listbox1 was a listbox on a worksheet in the original attachment.

When I look before, code posted was:

CustomerLookupUserForm.DateBox.Selected(I) = CustomerLookupUserForm.DateBox.List(I) >= DateSerial(2014, 1, 1) And Listbox1.List(I) <= DateSerial(2014, 3, 31)

It is different now.

See what you mean now, didn't spot that myself.

Why did you attach it to the original post?

I think this is a date formatting problem.

You may try:

Private Sub ComboBox1_Change()
    With Me.ListBox1
        If Me.ComboBox1.Value = "Manual" Then
            For I = 0 To .ListCount - 1
                .Selected(I) = False
            Next I
        End If

        If Me.ComboBox1.Value = "Quarter 1 (2014)" Then
            For I = 0 To .ListCount - 1
                .Selected(I) = CDate(.List(I)) >= DateSerial(2014, 1, 1) And CDate(.List(I)) <= DateSerial(2014, 3, 31)
            Next I
        End If
    End With
End Sub

Private Sub UserForm_Activate()
    Dim PF                         As PivotField
    Dim I                          As Integer
    Set PF = Worksheets("PivotTable").PivotTables(1).PivotFields("TxnDate")
    With Me.ListBox1
        .Clear
        For I = 1 To PF.PivotItems.Count
            .AddItem CDate(PF.PivotItems(I))
        Next
    End With


    With Me.ComboBox1
        .List = Array("Manual", "Quarter 1 (2014)")
        .ListIndex = 0
    End With

End Sub

Izandol

When I try that code, and my own similar code, it seems like the date formatting isn't quite right - for me anyway

For example, the next item after 12/1/2013 is 1/13/2013

That's followed by 1/14/2013 and continues on in the same format up to 1/31/2013.

@Norie
I too have this problem but I think OP is in USA so will not have it.
For me I will do this:

        For Each PI In PF.PivotItems
            vDate = Split(PI.Value, "/")
            .AddItem DateSerial(vDate(2), vDate(0), vDate(1))
        Next