VBA to add to Data Validation List

Hi,

Here is a recent question where I provided a workbook to demonstrate how to do this (not via a dedicated button, but by entry into a worksheet cell):

[ https://www.experts-exchange.com/questions/28263434/Excel-Data-Validation-Lists-If-choice-isnt-in-list-add-it.html?anchorAnswerId=39562395#a39562395 ]

The sample workbook I provided may be downloaded from the following location:

[ http://filedb.experts-exchange.com/incoming/2013/10_w41/682026/Q-28263434.xls ]


Would an approach like this address your requirements, or can it be adapted to suit your needs?

BFN,

fp.

Hi fp

I believe it will but yours is on the one sheet whereas mine are on separate Tabs [sheets]

Hi again fp

I presume I have to have the macro in the same sheet as the DropDown List. But I already have a macro in that sheet called Private Sub Worksheet_Change(ByVal Target As Range)
so I can't have two with the same name. I am not familiar [as I am a relative newby at this stuff] with these Private sub worksheet_change etc so can you help me out?

Thanks

In the example provided I was allowing the previous question asker to add to the list of data validation values themselves, or see the result of confirming the automatic addition to the list.

The list does not need to be on the same worksheet.  If on a separate worksheet, that worksheet does not need to be visible either.

Also, just because you already have code within the Worksheet_Change() event on one worksheet does not automatically negate the support of a second function that is handled when the worksheet changes.

This aside, I did not presume that what I demonstrated previously would match your requirements.  The provision of a button as you described above is also possible.

However, you have now alerted me to the fact that your existing workbook already contains some Visual Basic for Applications code, & that you are not familiar with adding/maintaining this.

Would you be able to attach (upload) your existing workbook in order to add the necessary changes to this rather than remotely instructing you what to do (without having sight of the workbook we are working with)?

Sorry fp another question I notice that yours only works in B1, when I copy your B1 into B2,B3 etc it does not work.

No, it won't do that.  As the original example was not designed to do that.

The ability to do that was not mentioned in your question text.  Is that a requirement?

The code within the Worksheet_Change() event specifically looks at changes to a named range [rngInput] that references (just) cell [B1].

It could monitor all of column [ B ], or a fixed range of cells, if required, however.


In providing the previous example, I seem to have confused you more than I intended to help!

As I said, if you can provide your existing workbook & confirm the pertinent details of operation/functionality, then a similar feature can be added to your own workbook.

Hi fp.

I am happy not to have a button, I like the way you do it. I do need the function to work in all cells from C5 down to C46 though.

Many thanks for your assistance.
Deliveries-Checklist.xlsm

Thanks for your file.

Just to confirm:

In cells [C5:C46] of the [Deliveries] worksheet, initially the drop-down data validation list will contain:

Matt
Hayley
Amarjohn
Audrey
Adam
Ricken

(These values will be taken from [H2:H7] within the [Static Data] worksheet)


However, if a value is entered that is not one of those above, then it will be automatically accepted & added to the list (added to column [H] in the [Static Data] worksheet) so that the drop-down data validation  list in any other cell within [C5:C46] of the [Deliveries] worksheet will then show the above six names plus the additional entry (or entries, for subsequent use).

Is that correct?  Or, do you wish to have a prompt to confirm the addition of the new name to the list?

Also, is it imperative that your list begins on row 2 of [Static Data], or could we move this up one row (to row 1)?

I can make the function work regardless, but starting on row 1 would be slightly less work (& would be easier to maintain if you ever wanted to investigate what I have done).

Hi fp,

In cells [C5:C46] of the [Deliveries] worksheet, initially the drop-down data validation list will contain:

Matt
Hayley
Amarjohn
Audrey
Adam
Ricken

Answer: Yes Correct

(These values will be taken from [H2:H7] within the [Static Data] worksheet)

Answer: Yes correct

However, if a value is entered that is not one of those above, then it will be automatically accepted & added to the list (added to column [H] in the [Static Data] worksheet) so that the drop-down data validation  list in any other cell within [C5:C46] of the [Deliveries] worksheet will then show the above six names plus the additional entry (or entries, for subsequent use).

Answer: When you say Automatically accepted..... I am would prefer to have it work the same way you original macro works where it asks 'Do you want to add it'


Is that correct?  Or, do you wish to have a prompt to confirm the addition of the new name to the list?

Answer:  I wish to have a prompt to confirm the addition of the new name to the list?

Also, is it imperative that your list begins on row 2 of [Static Data], or could we move this up one row (to row 1)?

Answer: I prefer the field to initially be blank but if that is a problem start at row 1


Thanks
Regards
Jagwarman

Hi fp are you able to review the file?

Thanks

Hi fp I am hoping you will be able to get back to me soon. In the meantime another thought crossed my mind. I liked the way you add to the list, is it also possible to remove from the list in the same way?

Thanks
Regards

Hi,

Sorry, I am presently trying to finalise/close all "my" outstanding questions at Experts-Exchange going back to April this year, but it is a case of too much to do/too little time to do it in!

This aside, what would be the method of removing an item from the list?

In the example I provided above, you can simply remove an entry from the list to the right of the worksheet; deleting the row, or copying'n'pasting entries lower down up one row to replace the existing entries (leaving a blank at the end of the list).

However, I suspect with your arrangement, that an [Add] button should complement a [Delete] button.

Would you like to think about this & let me know where these buttons should be placed or, indeed, what method you would (now) like to use?

Thanks.

BFN,

fp.

Hi fp,

sorry to add to your workload.

Not to worry about the delete part and nor am I bothered about a button. I like the way you did it in your file. However, as I previously mentioned
I do need the function to work in all cells from C5 down to C46 is it possible for you to do this?

Thanks.

:) No need to apologise.

I will look at your workbook this morning (UK time: currently 9:55am) & will comment again when I have something demonstrable for you.

Thanks for your patience.

brilliant thanks fp

I presume you are going to wait a week to close this question now as a 'punishment'! ;)

thought I had better make sure I don't want any more amendments :-)

Actually I thought I had closed it.

Have a great weekend and thanks for your help

You too, buddy :)