Automatically identifying new values and adding them to corresponding named range

Hello!

I´m currently working on a database and I´ve made an userform for new entries.

My ComboBoxes get their lists of choices from named ranges in a separate spreadsheet

My problem is:

Let's say I´m typing a new value (one that isn´t in the named range yet) in one of the CBox
I´d like that new value to also appear in the corresponding named range in that separate spreadsheet, so that next time, it would automatically appear in that very Cbox.

Is that possible?

Thanks!

Assuming you have a "Userform" with "Combobox1" and "CommandButton1" and your named range is called "data", then try this code in your Userform module.
If you wish to add the new "Combobox value" to the named range "data" then "Click" the CommandButton1. If it already exist a msgbox will tell you and then it will exit the sub.
NB:- If you are using the "Rowsource" to fill your range then Remove the reference to it in the Combobox properties window.

Please Login or Register  to view this content.

Regards Mick

Hey Mick, thank you for your reply!

From I understand, this code works in a way that necesitates a new button, and by clicking it, the value entered in the cbox goes directly to the named range.

Is there any way we don't need to press any new button, but instead that a formula would go fetch any new value (let's say in column A) in my DATA spreadsheet and add it automatically it to column A in my CODE (hidden) spreadsheet where are my named ranges?

(I've already used the OFFSET formula to make my ranges "dynamic", in case a new value needs to be added)

Thanks!

So... I've worked around your code and ended up with this:

Please Login or Register  to view this content.

I put it in my "OK button" Sub, works like a charm, thank you!

Oh by the way, if I have multiple named ranges, do I need to copy the whole thing over and declare rng, Dn, Dim etc... everytime and rename it (rng2, Dn2, Dim2...) or is there an simpler way to do it?

Actually I have a couple problems..

Whenever a named range is updated with the new value, my OFFSET formula gets kicked out for some reason, and goes back to "normal" :

Please Login or Register  to view this content.

Instead of :

Please Login or Register  to view this content.

Also, when I leave a Cbox blank, the code considers that blank as a new value and adds it to the named range:
no bueno!

Wait... Could those 2 issues be related? Did the OFFSET formula disapear because of the blank at the end of the named range...?

Any ideas ?

I added a bit to the code to solve the blank cell problem, seemed to do the trick.

Please Login or Register  to view this content.

But I still have the OFFSET formula problem whenever a named range is updated, which is kinda annoying...