Combobox reducing list vba 1

[owentmoore]

Hi all

I have a priority list of 1 to 28 which the user can select against any given product. As there are a number of products, there needs to be different priorities assigned and actions completed as a result etc.

I have created a combo box to do this for me. The combobox list is a range of numbers from 1 to 28. Using gotfocus the code reads the numbers already used from another range and removes these items from the combo box drop down if they are duplicated.

My code works - however - it is extremely long and I know it is very poorly written. I'm not using loops well at all. Can someone please suggest a shorter code!?!

Private Sub cbo0DCF1Priority_gotfocus()
Dim i As Long

Me.cbo0DCF1Priority.List = _
       Worksheets("ProductSelection").Range("Priority").Value

priority1 = Sheets("ProductSelection").Range("D33").Value
priority2 = Sheets("ProductSelection").Range("D34").Value

' In my code this line continues 26 more times!!!

For i = cbo0DCF1Priority.ListCount - 1 To 0 Step -1

   If cbo0DCF1Priority.List(i, 0) = priority1 Then
      cbo0DCF1Priority.RemoveItem (i)
   End If
Next

' In my code this is repeated 27 more times!!

End Sub

Thanks
Owen

 

[HughLerwill]

Because this is VBA you should be posting in forum707

However this may give you a start;

Dim priority(1 to 27) as integer, i as integer

For i = 1 to 27
priority(i) = Sheets("ProductSelection").Range("D" & i+6).Value
Next

 

[owentmoore]

Thanks Hugh. That's great start. I'll re-post in the correct thread if I can't figure out the rest.

Owen

 

[owentmoore]

I figured it out based on your post. Thank you!

Private Sub cbo0DCF1Priority_gotfocus()
Dim i As Long
Dim priority(1 To 28) As Integer, j As Integer

Me.cbo0DCF1Priority.List = _
       Worksheets("ProductSelection").Range("Priority").Value

For j = 1 To 28
   priority(j) = Sheets("ProductSelection").Range("D" & j + 32).Value
   
   For i = cbo0DCF1Priority.ListCount - 1 To 0 Step -1
   
   If cbo0DCF1Priority.List(i, 0) = priority(j) Then
      cbo0DCF1Priority.RemoveItem (i)
   End If

Next
Next

End Sub

 

[HughLerwill]

Now you can see the wood for the trees you may consider doing away with the priority array and just use;

If cbo0DCF1Priority.List(i, 0) = Sheets("ProductSelection").Range("D" & j + 32).Value

 

[owentmoore]

Cheers Hugh!