User Interface dropdown list index issue

[Mr_Ragweed]

Greetings, Hi, & Hello,
Many thanks to all of you who heave helped me get to this point in my project. I have one last hurdle to clear involving a UI.
It has 5 buttons (including the cancel). Basically it has a dropdown for "Department" that when selected populates the appropriate products into a dropdown called "Choose product". Once you select the product the user clicks the "Go To Product" button and is taken to the ws for that product. ON the product ws, the user does his work and thens hits an "Accept" button that send s his results to another results type sheet. All of that works fine.
My issue issue that once these steps have been taken for a product i would like to remove it from the "Choose Product" drop down and add it to the "Completed Products" dropdown. The list of products is dynamic. I'm using excel 2010. The code I have so far is posted below:
CODE STARTS HERE:

Private Sub btnCancel_Click()
Unload Me
End Sub
Private Sub btnGoToProduct_Click()
'push this button to go to the product sheet you want to set a formula for.
Dim strWSName As String
strWSName = ProductDropDown.Value
If ProductDropDown.Value = strWSName Then
Worksheets(strWSName).Activate
Unload Me
End If
End Sub
Private Sub CompletedProductDropDown_Change()
End Sub
Private Sub DeptDropDown_Change()

Dim ws As Worksheet
Set ws = Worksheets("PrdXDept")

Dim idx As Long
Dim arr As Variant
idx = DeptDropDown.ListIndex
If idx = -1 Then Exit Sub ' nothing selected, so exit
Select Case DeptDropDown.Value
Case "MASApp"
arr = ws.Range("MASApp")
Case "MASChm"
arr = ws.Range("MASChm")
Case "MASDry"
arr = ws.Range("MASDry")
Case "MASDrM"
arr = ws.Range("MASDrM")
Case "MASLiq"
arr = ws.Range("MASLiq")
Case "MASLiM"
arr = ws.Range("MASLiM")
Case "MASNon"
arr = ws.Range("MASNon")
Case "MASSee"
arr = ws.Range("MASSee")
Case "MASOth"
arr = ws.Range("MASOth")
Case "MASPre"
arr = ws.Range("MASPre")

End Select
ProductDropDown.List = arr

End Sub
Private Sub ProductDropDown_Change()
'need this to populate with all products by dept so maybe "if dept = x then get list" type stuff
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
'Populates the dept name drop down
Dim rngDeptName As Range
Dim ws As Worksheet
Set ws = Worksheets("Master Data")
For Each rngDeptName In ws.Range("Dept_Name")
Me.DeptDropDown.AddItem rngDeptName.Value
Next rngDeptName
End Sub

CODE ENDS HERE.
Many thanks. I dont even know what to search under in this forum, so my apologies if this is a redundant thread.

 

[Mr_Ragweed]

Not sure if i'm on the right track, but here goes...
A master index exists that populates the "Choose product" drop down. As the user "completes" products they are populated onto a new worksheet. I'm assuming i could use the new worksheet as the index for the "Completed products" dropdown. Is there a way the the master index could reference the new worksheet and "remove duplicates/ignore matches" before populating the "Choose Product" dropdown?
I really want to help whoever takes on this question, i'm just not sure i know how....

Thanks

 

[Mr_Ragweed]

Thought I would update with a visual aid of my goal: (where DD = Dropdown)
BEFORE products are edited:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Master Index(DD)[/TD]
[TD]Choose Product(DD)[/TD]
[TD]Completed Product(DD)[/TD]
[TD]Results Page(separate ws populated by a different UI)[/TD]
[/TR]
[TR]
[TD]Burger[/TD]
[TD] Burger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fries [/TD]
[TD]Fries[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shake[/TD]
[TD] Shake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


AFTER products are edited:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Master Index(DD)[/TD]
[TD]Choose Product(DD)[/TD]
[TD]Completed Product(DD)[/TD]
[TD]Results Page(separate ws populated by a different UI)[/TD]
[/TR]
[TR]
[TD]Burger[/TD]
[TD]Burger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Fries [/TD]
[TD]Fries [/TD]
[/TR]
[TR]
[TD]Shake[/TD]
[TD]Shake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The above, i think, is suggesting to use Match, where i would match the Results page against the Master Index and delete the match from the Master Index. The Completed Product would use the Results page as its index. Am i on to something?????

Even if no one responds - this "online diary/note taking in front of everyone" thing at least makes it feel like i'm gaining on it

Thanks for reading.