Solved

VBA to add to Data Validation List

Posted on 2013-11-01
19
401 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now