Avoid duplicated selection(s) across 15 comboboxes populated by a single range.

Hi Everyone, I'm fairly new to Excel VBA but I'm having a go at building a pricing model with vba user forms for selecting job materials etc. My cost_cat forms have 15 comboboxes for choosing items from a specific range. This is working ok but I want to avoid user duplication of items. Is it possible for combobox selections to be removed or hidden from the following comboboxes? My range is 3 columns but only column(1) (item description) populates the combobox list, whilst column(2) (cost code) and column(3) (pack type) populate an adjacent textbox and labelbox via the change routines for each combobox . Here is my no doubt, very basic code. Any help will be very much appreciated.

Dim xRange As Range

Private Sub UserForm_Initialize()

Set xRange = Worksheets("Engine").Range("ListPlaster")

Me.ComboBox1.List = xRange.Columns(1).Value
Me.ComboBox2.List = xRange.Columns(1).Value
Me.ComboBox3.List = xRange.Columns(1).Value
Me.ComboBox4.List = xRange.Columns(1).Value
Me.ComboBox5.List = xRange.Columns(1).Value
Me.ComboBox6.List = xRange.Columns(1).Value
Me.ComboBox7.List = xRange.Columns(1).Value
Me.ComboBox8.List = xRange.Columns(1).Value
Me.ComboBox9.List = xRange.Columns(1).Value
Me.ComboBox10.List = xRange.Columns(1).Value
Me.ComboBox11.List = xRange.Columns(1).Value
Me.ComboBox12.List = xRange.Columns(1).Value
Me.ComboBox13.List = xRange.Columns(1).Value
Me.ComboBox14.List = xRange.Columns(1).Value
Me.ComboBox15.List = xRange.Columns(1).Value

End Sub

Private Sub ComboBox1_Change()

TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
                xRange, 2, False)
LabelBox1.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
                    xRange, 3, False)
                      
End Sub

Private Sub ComboBox2_Change()

TextBox2.Text = Application.WorksheetFunction.VLookup(Me.ComboBox2.Value, _
                xRange, 2, False)
LabelBox2.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox2.Value, _
                    xRange, 3, False)
                      
End Sub

Private Sub ComboBox3_Change()

TextBox3.Text = Application.WorksheetFunction.VLookup(Me.ComboBox3.Value, _
                xRange, 2, False)
LabelBox3.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox3.Value, _
                    xRange, 3, False)
               
End Sub

'Etc for all comboboxes.

Quote from dangelor

Not exactly sure what you're asking...

If you mean removing duplicate items in your combo boxes, try this...

Code

Private Sub UserForm_Initialize()
    Dim e As Variant, arr As Variant

    Set xRange = WWorksheets("Engine").Range("ListPlaster")
    
    'create an array of unique items in column 1
    With CreateObject("Scripting.Dictionary")
        For Each e In Application.Index(xRange.Columns(1).Value, 0, 0)
            .Item(e) = Empty
        Next e
        arr = .keys
    End With
    
    Me.ComboBox1.List = arr
    ...
    Me.ComboBox15.List = arr
End Sub

Display More

Thanks and I apologise if my post led to some confusion. To clarify, I don't have duplications in my range, It is duplicate user selections that i'm trying to guard against by having combobox1 user selection beremoved from combobox2 list; combobox 1 & combobox2 user selections being removed from combobox 3 list etc.

Quote from royUK

Here's one way to prevent duplicate selections

Thanks Roy, I've used VLOOKUP because some of the range selections on following user forms will be quite long and would require lots of typing/maintenance if using AddItem. Will your code still work with VLOOKUP?

Quote from royUK

I need to see the workbook to understand why you are using VLOOKUP.

The code that I posted works on the ComboBoxes so that each combobox must have a different value.

Roy, Thanks for looking into this, copy workbook attached (no detailed modelling as yet). Copy workbook.xlsm

Quote from royUK

I've applied the code to UF_PlasterCodesP2. I've only added the code to the first two comboboxes, the rest need adding.

First of all I have simplified the loading of the ComboBoxes. Also, I have loaded all the data into the Comboxes, so the code can work without VLOOKUP which should improve the running of the code.

Also, to cut down coding I have used the ComboBoxes Tag Property. If you select ComboBox 1 or ComboBox2 then scroll down the Properties window on the left you will find the Tag Property. This can be used to store information, so I have added 1 to ComboBox1, 2 to ComboBox2. This needs doing for the other ComboBoxes.

The Tag is used to identify the corresponding TextBox and Label

Code

Sub EnterSelection()
''/// enters data for selected item in combobox
''/// does not need VLOOKUP or refer to sheet because all data has been loaded into the combobox
Dim iX As Integer


With Me.ActiveControl
iX = CInt(.Tag)
Me("TextBox" & iX).Value = .List(.ListIndex, 1)
Me("LabelBox" & iX).Caption = .List(.ListIndex, 2)
End With
End Sub

Display More

The ComboBox Change code needs copying and pasting into the remaining ComboBoxes.

I have also eliminated the need to use VLOOKUP to get the corresponding values.

Display More

Thanks Roy, I've applied your code across user forms PlasterCodesP1 and P2, and it works a treat. The P2 form is a continuation from P1 for instances when more than 15 of the PL code materials would be required. Is there any way that the P1 Function etc can carry over to P2 rather than it starting afresh in P2?

Roy, I've amended my code to move the ValueSelected function to a public module and it's working on both P1 and P2. But I may have my wires crossed! Does this just tidy things up in terms of coding other forms, or can the ValueSelected carry over from P1 to P2? I'm still using the add-on form at this stage rather than Multipage.

Cheers,

Woz.

Quote from royUK

You can use the function on any of the UserForms

Yes Roy, the function is working on both UF_PlasterP1 and P2 UserForms, and my other UF's are picking it up ok too. My problem is that I'm using P2 as an extension of P1 to add more materials using the same range as P1. But ValueSelected function starts afresh on P2 , thus P2 user selections can be duplicated from P1. So my question is can the "already selected" message show if a P2 selection was selected on P1 (without having to rebuild using multipage controls)?

I hope this better explains my problem.

Thanks,

Woz.

Quote from royUK

No, the code will not recognise the ComboBoxes on the other UserForm, you need to combine the two UserForms.

Thank you for all your help.

Woz.

I've now combined my two UserForms into one form using two frames. VBA code is working ok across the first frame but how can I keep the code and especially the ValueSelected function continuing across frame 2 into comboboxes 16 onward? I do like the look of the frame set up but it feels like I've solved one problem but created another!

COPY_TEST_FRAMES.xlsm

Woz.

Quote from royUK

I'm having problems with your workbook. I think the issue with your code is that you have put the comboboxes in Frames.

Does the userform and controls need to be so big?

Roy, I did wonder if the form would be too big for my son's MacBook! But my problem is that he (the user) will need to select more than 15 items (from the same range) in some of the cost categories and I thought that using the frames was a neat way of putting 30 choices on the one form. The smaller categories will work fine with a single userform and the 'Nodupe' coding that you kindly provided. But it appears that I will not be able to use any form of dupe control across say 25 comboboxes unless I split a cost category range into two, and then have one userform for each half. In which case I will need many userforms by the time I receive all of his cost categories.

Woz.