Userform Combobox duplicate hiding

[bbqsmokeman]

Hello

I have a Userform that has multiple Comboboxes and am trying to populate 2 comboboxes (cbAgent & cbAgentName) with names based on combobox (cbMU) but single names and not multiple. Right now they pull the data from worksheet GtoICheck (yes the sheet has each name multiple times cause it displays each person with what skills they have and this works perfectly) but I need to have cbAgent and cbAgentName show the name only once for each person and each MU depending on what number is chosen from cbMU (I do not want the duplicate names on the worksheet to be removed-just on the comboboxes)
I am also at a loss of how to get the listbox (lbData) to display the opposite. I want lbData to display all data but using .colorindex or .colorinterior to show red if there is a mismatch. I can do the conditional format on the worksheet but cannot figure out how to display it in the listbox and I can only do that once I figure out how to display the information.

I do have the code I have been trying to revise to make it work and its in the workbook I am supplying opposed to adding mutliple code on the site. I use to have an array in cbMU but that was alot of coding to add whats needed and names would require alot of constant updating if a agent moves to another group, etc.

Userform Initialize code below:

Private Sub UserForm_Initialize()
Dim v, e
With Sheets("GtoICheck").Range("d2:d500")
  v = .Value
End With
With CreateObject("scripting.dictionary")
  .comparemode = 1
  For Each e In v
  If Not .exists(e) Then .Add e, Nothing
  Next
  If .Count Then Me.cbAgentName.List = Application.Transpose(.keys)
End With




tbDate.Value = Now
tbDate = Format(tbDate.Value, "mm/dd/yyyy")
Me.cbMU.List = Worksheets("GtoICheck").Range("R2:R50").Value
Me.cbMuUpdated.List = Worksheets("GtoICheck").Range("R2:R50").Value
'Me.tbDate.Value = Format(Date, "mm/dd/yy")
'Me.tbDate.Text = Format(Now(), "mm/dd/yyyy")
With tbDate
  '.Text = "mm/dd/yyyy"
  '.SetFocus
  '.SelStart = 0
  '.SelLength = Len(.Text)

  cbGU.AddItem ""
  cbGU.AddItem "Sys1"
  cbGU.AddItem "Sys2"

  cbAuditDWM.AddItem ""
  cbAuditDWM.AddItem "Daily"
  cbAuditDWM.AddItem "Weekly"
  cbAuditDWM.AddItem "Monthly"

  cbSkFrom.AddItem ""
  cbSkFrom.AddItem "0"
  cbSkFrom.AddItem "1"
  cbSkFrom.AddItem "5"
  cbSkFrom.AddItem "9"

  cbSkTo.AddItem ""
  cbSkTo.AddItem "0"
  cbSkTo.AddItem "1"
  cbSkTo.AddItem "5"
  cbSkTo.AddItem "9"

  cbResetConfirm.AddItem ""
  cbResetConfirm.AddItem "Skill Change"
  cbResetConfirm.AddItem "Skill Reset"
  cbResetConfirm.AddItem "Name Spell Correction"
  cbResetConfirm.AddItem "Agent Removed"

End With



End Sub

cbMU code to fill comboboxes and listbox

Private Sub cbMU_Change()
Dim Rng As Range
Dim c As Range
Dim s As String


s = Me.cbMU

  Me.cbAgentName.Clear
  Me.cbAgent.Clear

  Set Rng = Worksheets("GtoICheck").Range("A:A").SpecialCells(xlCellTypeConstants, 23)

  For Each c In Rng.Cells
  If c = s Then
  Me.cbAgentName.AddItem c.Offset(, 1).Value
  Me.cbAgent.AddItem c.Offset(, 1).Value
  Me.cbSkName.AddItem c.Offset(, 2).Value
  Me.lbMSN.AddItem c.Offset(, 2).Value
  Me.lbData.AddItem c.Offset(, 1).Value

  End If
  Next c


End Sub

I will add a sample workbook shortly
In the workbook I have col K explaining details to ask / assistance needed.

Any help is greatly appreciated

 

[bbqsmokeman]

I thought I had the code to remove the duplicates in the combobox named cbAgentName but it didn't work.
I am not certain how to or if it's possible to add .removeduplicate in the following code as I keep getting errors

Me.cbAgentName.AddItem c.Offset(, 1).Value

So I attempted updating the vba code in the cbAgentName combobox using the following code but it does nothing and doesn't error out
What am I doing wrong or missing? I don't want the sheet duplicates removed. Just want individual names showing in the cbAgentName combobox
Here is the code I thought would work (not my code but adapted it to my userform and sheet)

Option Explicit


Sub cbAgentName_Change()
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 B2:B3000
Set AllCells = Range("B2:B3000")


' The next statement ignores the error caused

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

' 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 cbAgentName
For Each Item In NoDupes
ufSkillsAudit.cbAgentName.AddItem Item
Next Item

End Sub

Anyone have suggestions to my dilemna?
Ultimately if I can get this working I will adapt the working code to include the cbAgent combobox but thought it would be good to work on one at a time.
I have alot of work left on this userform and once the duplicate removal works then I am going to look at whoever I choose in cbAgent when clicked will show in the lbData listbox with only the values that have mismatches from the conditional format I have on the sheet; but not on the sample I supplied.

Thank you to anyone who is willing to help a learning older guy

 

[bbqsmokeman]

Hello everyone
I managed to get the vba working to remove duplicates and I am good to go so I consider this closed