Group dates according to the week or month

[Visor]

Dear friends of the forum how can I do so that when selecting the combobox3 only the dates that correspond only to the week selected in the combobox2 will be displayed but if this is not selected it will show only the dates of the month that is selected in the combobox1
Suboi a file to be seen
I appreciate your kind collaboration

 

[Visor]

I think is ok, because I have solved the issue, an expert sent me his solution

I only have one question, but I better upload a new topic
Thanks you

 

[Chihiro]

FYI - For those of you looking to do something similar as Op.

When making Dependent Combobox in Userform, never fill the combobox list, with exception of the first required. Otherwise, it will lock some properties of combobox and you'll have trouble trimming the list down. Instead, fill the dependent ones when first combobox selection is made.

 

[chirayu]

@Visor I'm glad you got a reply from someone to resolve your issue. But at the same time it sucks because I started working on it

In any case here's my version. It is a bit slow when using the dropdowns as it recalculates the Named Ranges in order to populate the ComboBoxes in the UserForm. Have a look.

@Chihiro see if you can improve on mine. You probably can because you're an excel whiz lol.

 

[Chihiro]

Personally... I'd use Listbox instead of Combobox if I need to update content of list.

If I had to use combobox, then I'd use array/dictionary to fill lists. And add reset button in userform to initialize settings. Will see what I'd come up with.

 

[Chihiro]

Ok, I think I've got working one. Though I haven't thoroughly checked.
Ended up not using Reset button.

You may want to optimize it by making some functions/subs in regular module.

Private Sub ComboBox1_Change()
Dim sDic As Object
Dim mNum As Integer, i As Long
j = 1
With Sheets("ACAD")
    sA = .Range("A6:B" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With

Set sDic = CreateObject("Scripting.Dictionary")
Set rDic = CreateObject("Scripting.Dictionary")
With ComboBox1
    mNum = Application.Match(.Value, .List, 0)
    For i = 1 To UBound(sA)
        If Month(sA(i, 2)) = mNum Then
            sDic(sA(i, 1)) = 1
            rDic(sA(i, 2)) = 1
        End If
    Next
End With

If sDic.Count = 0 Then
    ComboBox2.Clear
    ComboBox2.AddItem "No Match Found"
Else
    ComboBox2.List = sDic.Keys
    ComboBox3.List = rDic.Keys
End If
Set sDic = Nothing
Set rDic = Nothing
End Sub

Private Sub ComboBox2_Change()
Dim sA, rDic As Object, mNum
Dim i As Long
With Sheets("ACAD")
    sA = .Range("A6:B" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With

If ComboBox2 = "" Then
Else
'' Use only if you need to match both Month and Week num to date.
'    With ComboBox1
'        mNum = Application.Match(.Value, .List, 0)
'    End With
    With ComboBox3
        .Clear
        For i = 1 To UBound(sA)
            If CStr(sA(i, 1)) = ComboBox2.Value Then ' add condition if you want both week num and month to match "And Month(sA(i, 2)) = mNum"
                .AddItem sA(i, 2)
            End If
        Next
    End With
End If

End Sub

Private Sub ComboBox3_Change()

If ComboBox3 = "" Then
Else
    ComboBox3.Value = Format(ComboBox3.Value, "DDD DD-MMM-YY")
End If

End Sub

Private Sub UserForm_Initialize()

ComboBox1.List = Array("Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre")

End Sub

 

[chirayu]

@Chihiro Me Likey Very Much. No lag whatsoever unlike mine and yeah I get what you mean by not thoroughly checked. Found some minor things that are easy fixes. I've never used scripting dictionary so no clue what it does to be honest. Neither do I know what CStr or .Keys do.

 

[Chihiro]

CStr is used to convert numeric to string. This is done, to ensure data type match. So to compare string to string.

You could do CLng or CInt on Combobox.Value as well, if needed.

.Keys is part of dictionary object model.

Dictionary is much like Collection object model, but has few key differences.
1. Can set compare mode to Text (case insensitive) or binary (case sensitive)

2. In dictionary item and key properties are writable, where as in collection, you need to remove the item and add in the new one.

3. dic.Keys & dic.Items are zero base, one dimensional array. So it's easy to put it into array or use as one.

4. You can implicitly add item. Like I did in the code.

sDic(sA(i, 1)) = 1

In collection you need to explicitly add item.

myCol.Add 10, "myKey"

5. In dictionary you can retrieve key. But in collection you can only use to lookup data.

6. Dictionary isn't native to VBA. Need custom Class module in MAC. On the other hand, dictionary can be used in VBS, but not collection.

7. Dictionary has native .Exists method to check if key is already present.
Etc, etc.

You can find good starting tutorial in link below.
http://analystcave.com/excel-vba-dictionary-arrays-and-other-data-structures/

 

[Visor]

Excellent, I am going to read, and after I will coment

 

[Visor]

Indeed Chirayu is too slow but the shortest way is sometimes better, discovering what can be many ways and that is the art of this ..
Thanks Chihiro is how I was looking for the macro to work and thanks for the dictionary code, it's great that the link
Solved theme

 

[chirayu]

@Chihiro read through the link you provided and I understand your code now

CombBox1_Change =
sA loaded columns A:B as range
mNum figures out the position of the selected value in combobox
Month(sA(i,2)) = mNum checks whether the dates in column B have same month number as mNum
sDic & rDic load column A/column B values as keys
which later get populated back in combox2 and combobox3

ComboBox2_Change =
Similar sorta stuff to 1st one but clears stuff populated by rdic

 

[Visor]

Es verdad, es genial, estoy leyendo el diccionario vba, es interesante. Además, con lo que la resolución, me ha guiado para hacel algo similar con estos combobox. Pero prefiero cargar otro tema con el archivo de ejemplo