Solved

VBA to add to Data Validation List

Posted on 2013-11-01
19
420 Views
Last Modified: 2013-11-08
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
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39616315
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
 

Author Comment

by:Jagwarman
ID: 39616331
Hi fp

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

Author Comment

by:Jagwarman
ID: 39616353
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39616404
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
 

Author Comment

by:Jagwarman
ID: 39616414
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39616433
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
 

Author Comment

by:Jagwarman
ID: 39616439
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39616518
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
 

Author Comment

by:Jagwarman
ID: 39616626
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
 

Author Comment

by:Jagwarman
ID: 39621226
Hi fp are you able to review the file?

Thanks
0
 

Author Comment

by:Jagwarman
ID: 39623847
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39624411
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
 

Author Comment

by:Jagwarman
ID: 39624466
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39626751
:) 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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39627178
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
 

Author Comment

by:Jagwarman
ID: 39629771
brilliant thanks fp
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39632988
I presume you are going to wait a week to close this question now as a 'punishment'! ;)
0
 

Author Comment

by:Jagwarman
ID: 39633003
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39633130
You too, buddy :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question