VBA to add to Data Validation List

Is there any code that I can use to add items into an existing Drop down List?

I have a list in a Sheet named Static Data [which is a hidden sheet]. The List Name is Responsibility and the data for this is in column H.

The user uses a sheet called Deliveries and I would like to add a button on this sheet that will call the macro to give the user the opportunity to add additional Names in the List.

Thanks
JagwarmanAsked:
Who is Participating?
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Again, sorry this took as long as it did; the changes were trivial when I looked into what I had already coded (for the earlier question).

This code now resides within the code module for the [Deliveries] worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28282865.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28282865
' Question Title:   VBA to add to Data Validation List
' Question Asker:   Jagwarman                                 [ http://www.experts-exchange.com/M_6499824.html ]
' Question Dated:   2013-11-01 at 10:09:32
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited    [ http://linkedin.com/in/ITServicesConsultant ]
'
' Based on:
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28263434.html ]
' --------------------------------------------------------------------------------------------------------------

  On Error GoTo Err_Worksheet_Change
  
  If Not (Intersect(Target, [C5:C46]) Is Nothing) Then
     If Len(Trim$(Target.Value)) > 0 Then
        If ([rngList].Find(What:=Target.Value, LookAt:=xlWhole) Is Nothing) Then
           Beep
           If MsgBox("Add " & Chr$(34) & Target.Value & Chr$(34) & " to Data Validation list?", _
                     vbQuestion Or vbYesNo, _
                     ThisWorkbook.Name) = vbYes Then
             [rngList].Cells(1&).Offset([rngList].Rows.Count) = Target.Value
          Else
             Target.Value = ""
          End If ' If MsgBox(...) = vbYes Then
          
          Target.Select
       End If ' If ([rngList].Find(What:=Target.Value) Is Nothing) Then
    End If ' If Len(Trim$(Target.Value)) > 0 Then
  End If ' If Not (Intersect(Target, [C5:C46]) Is Nothing) Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change
  
End Sub

Open in new window



The [rngList] named range (within the [Static Data] worksheet) is defined as:
=OFFSET('Static Data'!$H$1,0,0,COUNTA('Static Data'!$H:$H)+1,1)

A revised workbook is attached for your review.

BFN,

fp.
Q-28282865.xlsm
0
 
[ fanpages ]IT Services ConsultantCommented:
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):

[ http://www.experts-exchange.com/Microsoft/Applications/Q_28263434.html#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.
0
 
JagwarmanAuthor Commented:
Hi fp

I believe it will but yours is on the one sheet whereas mine are on separate Tabs [sheets]
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
JagwarmanAuthor Commented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
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)?
0
 
JagwarmanAuthor Commented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
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.
0
 
JagwarmanAuthor Commented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
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).
0
 
JagwarmanAuthor Commented:
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
0
 
JagwarmanAuthor Commented:
Hi fp are you able to review the file?

Thanks
0
 
JagwarmanAuthor Commented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
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.
0
 
JagwarmanAuthor Commented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
:) 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.
0
 
JagwarmanAuthor Commented:
brilliant thanks fp
0
 
[ fanpages ]IT Services ConsultantCommented:
I presume you are going to wait a week to close this question now as a 'punishment'! ;)
0
 
JagwarmanAuthor Commented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
You too, buddy :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.