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.

  • You can remove items from a comboox, but only if they are added using AddItem.


    I wouldn't use VLOOKUP as you have.


    Attach an example workbook and I'll take a look.

  • Not exactly sure what you're asking...

    ...Is it possible for combobox selections to be removed or hidden from the following comboboxes?...

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

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Not exactly sure what you're asking...

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

    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.

  • 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?

  • 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.

  • 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

  • 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


    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.

  • 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?

  • i would use a MultiPage control on the one UserForm for the continuation instead of two.


    I've moved the ValueSelected Function from the UserForm to a standard module and madde it a public function that can be used on any UserForm in the workbook

  • 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.

  • 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.

  • 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?

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!