All,
I've completed the task I would like to achieve, however, I am wondering if it is possible to complete the task of actively removing duplicates from a cascading combobox without creating a new sheet in the workbook. This appears to slow down UI operation quite drastically.
VBA Code Below;
I've completed the task I would like to achieve, however, I am wondering if it is possible to complete the task of actively removing duplicates from a cascading combobox without creating a new sheet in the workbook. This appears to slow down UI operation quite drastically.
VBA Code Below;
VBA Code:
Private Sub combobox7_Change()
Dim myval As String
Dim lr As String
Dim x As Long
myval = Me.ComboBox7
'loop thru col a
lr = ThisWorkbook.Sheets("paste log").Cells(Rows.Count, 1).End(xlUp).Row
'clear combobox 11
Me.ComboBox11.Clear
For x = 2 To lr
If myval = ThisWorkbook.Sheets("paste log").Cells(x, 1) Then
'add to combobox
Me.ComboBox11.AddItem ThisWorkbook.Sheets("paste log").Cells(x, "b")
End If
Next x
Dim iCntr As Long
Dim recCountBefore As Long
Dim lRow As Long
Dim tmpsht As Worksheet
recCountBefore = Me.ComboBox11.ListCount
Set tmpsht = ThisWorkbook.Worksheets.Add
For iCntr = 0 To recCountBefore - 1
tmpsht.Cells(iCntr + 1, 1) = Me.ComboBox11.List(iCntr)
Next
tmpsht.Columns(1).RemoveDuplicates Columns:=Array(1)
lRow = tmpsht.Range("A60000").End(xlUp).Row
ComboBox11.Clear
For iCntr = 1 To lRow
ComboBox11.AddItem tmpsht.Cells(iCntr, 1)
Next
Application.DisplayAlerts = False
tmpsht.Delete
Application.DisplayAlerts = True
End Sub