Excel - Data Validation Lists; If choice isnt in list, add it?

Using a data validation list, if a user types in a value that isnt on the list, can they be given a choice to add that value to the list so it will be there next time?prompt the user, asking if they would like to add an to the list so it would be there next time?

Also, the font size of the dropdown list is very small.  Can that be changed?
Michael SpellmanSupervisory Operations Support SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Also, the font size of the dropdown list is very small.  Can that be changed?

No, sorry.  However, as I posted in another question thread, yesterday:

[ http://www.experts-exchange.com/Microsoft/Applications/Q_28261957.html#a39558100 ]
---
PS. For background reading, Debra Dalgleish has some information on her site about Data Validation (& variants for implementing drop-down lists):

[ http://blog.contextures.com/archives/2010/02/03/3-types-of-excel-drop-down-lists-compared/ ]

[ http://www.contextures.com/xlDataVal08.html ]

For particular reference to your point #2 above.

"Make the Dropdown List Appear Larger"
[ http://www.contextures.com/xlDataVal08.html#Larger ]
---

BFN,

fp.
0
[ fanpages ]IT Services ConsultantCommented:
Using a data validation list, if a user types in a value that isnt on the list, can they be given a choice to add that value to the list so it will be there next time?prompt the user, asking if they would like to add an to the list so it would be there next time?

Yes, that is possible.

It would be easier if the list was taken from a range of cells elsewhere in the workbook (as there is a maximum limit to the number of characters that can be used in an explicit list) & the additional item was added to this range (& referenced by a dynamic named range), but what you ask is possible.

Please find attached a workbook to demonstrate what can be achieved with a list elsewhere in the worksheet.

The code from the "ThisWorkbook" ("wbkQ_28263434") code module is as follows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28263434.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28263434
' Question Title:   Excel - Data Validation Lists; If choice isnt in list, add it?
' Question Asker:   mspellm                                   [ http://www.experts-exchange.com/M_2377888.html ]
' Question Dated:   2013-10-10 at 14:39:48
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited    [ http://linkedin.com/in/ITServicesConsultant ]
' --------------------------------------------------------------------------------------------------------------

' --------------------------------------------------------------------------------------------------------------
' Additional code by ssaqibh [ http://www.experts-exchange.com/M_955320.html ], taken/re-coded from:
'
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28262174.html ]
' --------------------------------------------------------------------------------------------------------------

  Dim objRange                                          As Range
  
  On Error GoTo Err_Worksheet_Change
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  Select Case (True)
  
      Case (Target.Address = [rngInput].Address)
          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
         
    Case (Not (Intersect(Target, Columns([rngList].Cells(1&).Column)) Is Nothing))
        If Len(Trim$(Target.Value)) = 0 Then
           Columns([rngList].Cells(1&).Column).Select
           Selection.EntireColumn.Insert
           
           Set objRange = Intersect(Selection.Offset(, 1).EntireColumn, ActiveSheet.UsedRange).Offset(1&)
           
           objRange.SpecialCells(xlCellTypeConstants).Offset(, -1).Formula = "=ROW()"
           objRange.Offset(, -1).Value = objRange.Offset(, -1).Value
           objRange.Offset(, -1).Resize(, 2).Sort Key1:=objRange.Offset(, -1).Cells(1, 1), Order1:=xlAscending
           objRange.Offset(, -1).EntireColumn.Delete
        End If ' If Len(Trim$(Target.Value)) = 0 Then
           
        If ([rngList].Find(What:=[rngInput].Value, LookAt:=xlWhole) Is Nothing) Then
           Beep
           [rngInput].Value = ""
        End If ' If ([rngList].Find(What:=Target.Value, LookAt:=xlWholeW) Is Nothing) Then
           
        Target.Select
        
    Case Else
           
  End Select ' Select Case (True)
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Set objRange = Nothing
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change
  
End Sub

Open in new window



(Thanks to ssaqibh).
Q-28263434.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.