Solved: Dynamically Expand Validation Dropdown List

[Dadmin]

Hello, experts. I have a named range pointing to a validation list. I want to dynamically expand this list by way of the Error message that pops up when a user enters invalid data (i.e. - data not already in the list.) I want the error message to ask the user if they want to add the 'invalid' value to the validation list, thereby making it now valid. This would allow the user to have some semblance of validation, while not being encumbered with having to go to the list and manually expand it. Does this have a solution?:think:

[GTO]

Greetings,

I would think this could get messy, but 'til a cleaner answer comes along...

By example, let's say the area we have applied validation to is A1:A5. Presuming you have the Error Alert set to Warning so that the user can override the validation...

In the Worksheet's Module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngList As Range
Dim strAddr As String

If Not Application.Intersect(Target, Range("A1:A5")) Is Nothing _
And Target.Count = 1 Then
strAddr = Range("A1").Validation.Formula1

Set rngList = Range(strAddr)

If Target.Validation.Value = False Then
If MsgBox("Would you like to add " & Target.Text & _
" this to the Validation list?", vbYesNo, "") = vbYes Then

Set rngList = rngList.Resize(rngList.Rows.Count + 1)
rngList(rngList.Rows.Count, 1).Value = Target
Range("A1:A5").Validation.Modify , , , "=" & rngList.Address
End If
End If
End If
End Sub


In my little bit of testing, this seems to work.

Hope that helps,

Mark

[mdmackillop]

This could be simplified slightly if you used a Dynamic Range Name for the validation list.

[Dadmin]

GTO and mdmackillop,

Thank you for your response. I think I want to try the suggestion mdmackillop made of a Dynamic Range Name. Attached is a sample workbook with two worksheets. Worksheet "PM" is the database, with column A as a counter and column B is the data field with Validation turned on. The validation criteria is "List", with the source set to a dynamic range name pointing to my list of valid entries on the second worksheet called "Validation".
When I position the cursor in cell B4, I have the option of selecting an entry from the validation dropdown list, or I can type in a value. I set the Error Alert style to "Warning" so that if the value does not match the validation list, I get the warning message saying, "Data not valid" and a prompt asking to Continue "Yes", "No" or "Cancel". What I want Excel to do when I click "Yes" is to add the value I just typed to the validation list in the "Validation" worksheet. Then the dynamic named range will automatically pick up the new entry and display it in the dropdown list in my main database. Does this make sense to you?

BTW, I'm running Excel 2002. The number of validation values would be in the dozens and the number of records would be in the hundreds.

Thanks

[Zack Barresse]

Here's another spin (code goes in your PM sheet code module)...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsValidation As Worksheet, rValidation As Range, sValidation As String
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
On Error Resume Next
If Target.Validation.Type <> 3 Then Exit Sub
sValidation = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
Set rValidation = ThisWorkbook.Sheets("Validation").Range(sValidation)
If WorksheetFunction.CountIf(rValidation, Target.Value) = 0 Then
Application.EnableEvents = False
rValidation(1, 1).End(xlDown).Offset(1, 0).Value = Target.Value
Application.EnableEvents = True
End If
End Sub

Checks for single-cell entry, column B only, duplicate named range values.

HTH

[Dadmin]

Zack,
Sorry I couldn't get back to you sooner, but I've been busy. I tried your code out and it does the trick. I'd like to mark this thread "Solved" but I don't know how. Thanks to everyone who replied.

Regards,
Steve

[Dadmin]

Aha! I just solved the "Solved" mystery.