Sorted Data Validation List on Another Sheet code very wordy.

[leaning]

Hello!

I have a clunky code which takes the value in cell C5 on SHEETB and uses it for a dropdown validation list from a named range on SHEETA.
If the C5 value is not on the dropdown, it asks you if you want to add it, so the next time you use that worksheet, you won't have to re-type that new value.

So as new values get added, the dropdown list grows and it does a sort so that the list is always in alphabetical order.

I'd appreciate it if someone can look at this code. I used the macro recorder alot, and this result works but it seems to me like some of the steps can be combined. And then I set the range to 10,000 entries so I'm not sure what's going to happen if there's 10,001 (will that one not get sorted?), etc.

Regards,
Leaning

Please Login or Register  to view this content.

[skywriter]

I would do it differently, but obviously the choice is yours.
In data validation I would have this.
Source:
=SHEETA!$D:$D

Then I would have the following code:

Please Login or Register  to view this content.

[leaning]

Skywriter: That's awesome! It works tons different and smoother without the screen flickering all over the place from changing sheets.
I appreciate your help. I think you chopped almost half the lines of code, so yeah, it needed some work. Mad props!

Regards,
Leaning

[leaning]

Skywriter,

I took the example workbook you fixed and tried to apply it to the actual workbook. It works great, but the dropdown in the cell has a bunch of extra white space in it that I can't tell where it's coming from. Any ideas? All PWs are "help", BTW.
Regards,
Leaning

[skywriter]

Your project is locked, I can't see the code.

[leaning]

skywriter: the protect, unprotect, and VBA passwords are all "help". My actual project is locked so I wanted to see if maybe the lock was causing problems and creating that white space.

HTH.
Regards,
Leaning

[skywriter]

Your location is spelled wrong, you are in Michigan?

If you are happy with my help a bump of the reputation button would be appreciated.

Please Login or Register  to view this content.

[leaning]

skywriter: still not working on my end. Dropdown still has extra spaces. Any other ideas? I highlighted all the data cells purple, but it doesn't look like there is anything below that is making the dropdown list so long. I'm stumped. Thanks for sticking with me through this. And I updated my location. Thanks for that!

Regards,
Leaning

[leaning]

Duplicate post

[leaning]

I kept getting blank lines in the dropdown using skywriter's code, so I'm back to my clunky code:

Please Login or Register  to view this content.

If anyone has any ideas on making this better and less flickering, I'd appreciate it.

Regards,
Leaning

[leaning]

This is better:

Please Login or Register  to view this content.