VBA Dropdown

[sgrzzl]

I have a vba user form with several excel drop down lists. I would like the dropdowns to display a list of products. If selected in another dropdown I would like the product to be removed from the other dropdowns.

I have the validation working in the workbook using the worksheets and can setup drop downs that work correctly within a worksheet but when I try to initialize the drop downs in my vba form using the same method I loose the validation.

My VBA drop down lists initialize and show the correct items in my list but aren't removing items that have already been selected.

Here is my code:

Private Sub UserForm_Initialize()
MultiPage2.Value = 0
With MultiPage2.Pages(0).Controls("HostedPBXdesc1")
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With

HostedPBXdesc1.List = Range("prodSelect").Value
HostedPBXdesc2.List = Range("prodSelect").Value
HostedPBXdesc3.List = Range("prodSelect").Value
HostedPBXdesc4.List = Range("prodSelect").Value
HostedPBXdesc5.List = Range("prodSelect").Value
HostedPBXdesc6.List = Range("prodSelect").Value
HostedPBXdesc7.List = Range("prodSelect").Value
HostedPBXdesc8.List = Range("prodSelect").Value
HostedPBXdesc9.List = Range("prodSelect").Value
HostedPBXdesc10.List = Range("prodSelect").Value
HostedPBXdesc11.List = Range("prodSelect").Value
HostedPBXdesc12.List = Range("prodSelect").Value
HostedPBXdesc13.List = Range("prodSelect").Value

I'm not sure if that is very clear.

 

[sgrzzl]

O.k. I got the validation to work on my vba dropdowns. The dropdowns populate with my list when I select items from the list they are removed from the other drop downs. I set the rowSource for my dropdown to my named range using the following code:

Me.HostedPBXdesc1.RowSource = "prodSelect"


I am only having one problem now. It will not let me select the first value from the list. When I click on the first value the second item on the list is selected. I'm not sure what is going on here.

Here is the code for my named range:

prodSelect
=OFFSET(prod!$C$1,0,0,COUNTA(prod!$C$1:$C$13)-COUNTBLANK(prod!$C$1:$C$13),1)

This code references my "prod" worksheet where I have a list of products and 2 columns with validation formulas

Column A: Product List (13 items)
Column B: =IF(COUNTIF(HostedPBX!$C$8:$C$20,A1)>=1,"",ROW())
Column C: =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$13),"",INDEX(A:A,SMALL(B$1:B$13,1+ROW(A1)-ROW(A$1))))