Fasten code (UserForm)

[Digitborn.com]

Hello,

I have a piece of code which works alright. My question is if it's possible to change the code and make it work faster. When I change the ComboBox I wait about 1-3 seconds for about 40 items in the Worksheet. I still didn't test it with more items. Here's the code:

Private Sub ComboBox1P2_Change()
    If ComboBox1P2.ListIndex = 0 Then
        ComboBox2P2.Style = fmStyleDropDownCombo
        ComboBox2P2.Value = sign
        ComboBox2P2.Enabled = False
        ComboBox3P2.Clear
        ComboBox3P2.Enabled = False
        ComboBox1P2.SetFocus
    ElseIf ComboBox1P2.ListIndex <> 0 And ComboBox1P2.ListIndex <> -1 Then
        ComboBox2P2.Style = fmStyleDropDownList
        ComboBox2P2.Enabled = True
        ComboBox3P2.Clear
        ComboBox3P2.Enabled = False
        ComboBox2P2.SetFocus
        With Worksheets("PartsData")
        For Each Combo2P2 In .Range("A2", .Range("A65536").End(xlUp))
            If Len(Me.ComboBox1P2) > 0 Then
                Dim dic As Object, i As Long, d, a, z, y
                z = Me.ComboBox1P2.Value
                Set dic = CreateObject("Scripting.Dictionary")
                dic.CompareMode = vbTextCompare
                    With .Range("A2:BJ" & .Range("A" & Rows.Count).End(xlUp).Row)
                        .Sort .Range("A2"), xlAscending
                        a = .Offset(0, 0).Resize(, 2).Value
                    End With
                For i = 1 To UBound(a, 1)
                    If Not IsEmpty(a(i, 1)) Then
                        d = Format(a(i, 1), "dd-mmm-yy")
                        If Not dic.exists(d) And a(i, 2) = z Then dic.Add d, Nothing
                    End If
                Next: y = dic.keys: Set dic = Nothing: Erase a
                If UBound(y) < 0 Then
                    With Me.ComboBox2P2
                        .Style = fmStyleDropDownCombo
                        .Value = sign
                        .Enabled = False
                    End With
                Else
                    With Me.ComboBox2P2
                        .Enabled = True
                        .Clear
                        .List = y
                    End With
                End If
            End If
        Next
        End With
    End If
End Sub

 

[Norie]

Where is this combobox located?

Why are you using the change event to populate it?

 

[Digitborn.com]

ComboBox1P2 is located on a UserForm, MultiPage1.Page2

I use ComboBox1P2 to populate ComboBox2P2 because I have the following situation:

1. ComboBox1P2 - projects
2. ComboBox2P2 - dates

Depending on the project I choose from ComboBox1P2, ComboBox2P2 lists all availables dates for this project.

These are cascading ComboBoxes which populate on different conditions throughout my UserForm.

 

[tusharm]

You can start by taking the time to understand how loops work. When one writes a loop (For Combo2P2 in .Range... in your case), the loop control variable (Combo2P2 in your case) should play a role in the processing inside the loop. In your case it doesn't. So, you are doing the exact same thing each time through the loop!

Hello,

I have a piece of code which works alright. My question is if it's possible to change the code and make it work faster. When I change the ComboBox I wait about 1-3 seconds for about 40 items in the Worksheet. I still didn't test it with more items. Here's the code:

Private Sub ComboBox1P2_Change()
    If ComboBox1P2.ListIndex = 0 Then
        ComboBox2P2.Style = fmStyleDropDownCombo
        ComboBox2P2.Value = sign
        ComboBox2P2.Enabled = False
        ComboBox3P2.Clear
        ComboBox3P2.Enabled = False
        ComboBox1P2.SetFocus
    ElseIf ComboBox1P2.ListIndex <> 0 And ComboBox1P2.ListIndex <> -1 Then
        ComboBox2P2.Style = fmStyleDropDownList
        ComboBox2P2.Enabled = True
        ComboBox3P2.Clear
        ComboBox3P2.Enabled = False
        ComboBox2P2.SetFocus
        With Worksheets("PartsData")
        For Each Combo2P2 In .Range("A2", .Range("A65536").End(xlUp))
            If Len(Me.ComboBox1P2) > 0 Then
                Dim dic As Object, i As Long, d, a, z, y
                z = Me.ComboBox1P2.Value
                Set dic = CreateObject("Scripting.Dictionary")
                dic.CompareMode = vbTextCompare
                    With .Range("A2:BJ" & .Range("A" & Rows.Count).End(xlUp).Row)
                        .Sort .Range("A2"), xlAscending
                        a = .Offset(0, 0).Resize(, 2).Value
                    End With
                For i = 1 To UBound(a, 1)
                    If Not IsEmpty(a(i, 1)) Then
                        d = Format(a(i, 1), "dd-mmm-yy")
                        If Not dic.exists(d) And a(i, 2) = z Then dic.Add d, Nothing
                    End If
                Next: y = dic.keys: Set dic = Nothing: Erase a
                If UBound(y) < 0 Then
                    With Me.ComboBox2P2
                        .Style = fmStyleDropDownCombo
                        .Value = sign
                        .Enabled = False
                    End With
                Else
                    With Me.ComboBox2P2
                        .Enabled = True
                        .Clear
                        .List = y
                    End With
                End If
            End If
        Next
        End With
    End If
End Sub

 

[Norie]

Tushar

Good spot.

Digitborn

Why this?

For Each Combo2P2 In .Range("A2", .Range("A65536").End(xlUp))

That just doesn't make sense.

For a start you don't declare Combo2P2 anywhere, though I do realise it's probably a control on your userform.

Now because of the non declaration and the fact it's a control on the userform I'm not sure how VBA is going to treat it.

 

[tusharm]

{snip}

Now because of the non declaration and the fact it's a control on the userform I'm not sure how VBA is going to treat it.

How do you conclude it is a control on the userform?

 

[Norie]

Eh, because the OP seems to be indicating that.

These are cascading ComboBoxes which populate on different conditions throughout my UserForm.

I also see little reference to worksheet controls in the posted code.

 

[tusharm]

It wouldn't hurt to actually look at the code before jumping to conclusions. The control is ComboBox2P2, the loop control variable is Combo2P2. Notice the difference?

Eh, because the OP seems to be indicating that.

These are cascading ComboBoxes which populate on different conditions throughout my UserForm.

I also see little reference to worksheet controls in the posted code.

 

[Norie]

Tushar

Would you kindly point out to me where a control on a worksheet is mentioned in either the OP's posts or the posted code?

These are cascading ComboBoxes which populate on different conditions throughout my UserForm.

 

[tusharm]

Norie,

What are you going on about?

For the last time, look at the difference in the names of the loop control variable and the userform control.

Tushar

Would you kindly point out to me where a control on a worksheet is mentioned in either the OP's posts or the posted code?

These are cascading ComboBoxes which populate on different conditions throughout my UserForm.