Below is my code that works great and gives me a unique list for combobox2 on userform1. The problem I am having is when you run the userform1 and the user is making their selections in the various boxes on the userform, after making a selection with the dropdown on combobox2 their selection go into the box. However, you can still see part of the drop down list from that combobox behind the userform.
It stays visible until the user selects ok or cancel....can someone tell me why?
Help!
It stays visible until the user selects ok or cancel....can someone tell me why?
Help!
Code:
Sub Show_Userform1()
'
' Show_Userform1 Macro
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
' The items are in A1:A105
Set AllCells = Range("C2:C500")
' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell
' Resume normal error handling
On Error GoTo 0
' Update the labels on UserForm1
'With UserForm1
' .Label1.Caption = "Total Items: " & AllCells.Count
' .Label2.Caption = "Unique Items: " & NoDupes.Count
'End With
' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
UserForm1.ComboBox2.AddItem Item
Next Item
Sheets("Home").Select
UserForm1.Show
End Sub