Digitborn.com
Active Member
- Joined
- Apr 3, 2007
- Messages
- 353
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:
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:
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
