Data Validation

blatham

New Member
Joined
Nov 8, 2005
Messages
47
I have a cell with data validation on it that refers to a dynamic named range. I would like to append the choice "New" to the list of valid entries. Is it possinle to do this as part of the data validation set up for the cell as opposed to adding this entry to the range on which it is based?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi, blatham
Welcome to the Board !

try to type this in the source
=A5,A6
errormessage will be: union not allowed !

what are you trying to do ?
perhaps use listbox which can be filled with data from anywhere
or another workaround ...

ready to help further if needed

kind regards,
Erik
 
Upvote 0
The range on which it is based is already dynamic, that is irrelevant though really. I want the entries in the named range to remain the same as this provides the source for other cell validation. For this particular cell however I want the user to have the option of selecting "New". I could set up another range which is the same apart from this additional entry, but I thought it would be neater if I was able to stick with the one and do something fancy in the validation set up.
 
Upvote 0
If for example...

- Your data validated cell that you want to append with "New" is cell A1 on Sheet1
- Your dynamic list is named "MyList", housed on Sheet2

...then see if this helps. Right click on your Sheet1 sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet. Modify for the actual sheet names, target address, and named range.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
Application.ScreenUpdating = False
Dim cell As Range, strList$, strEnd$
strList = ""
For Each cell In Sheets("Sheet2").Range("MyList")
strList = strList & cell.Value & ","
Next cell
strEnd = "New," & Mid(strList, 1, Len(strList) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=strEnd
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "No such animal !!"
.ErrorMessage = "Please select a valid item" & Chr(10) & _
"from the drop-down list."
.ShowError = True
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
That's a solution, cheers for that, I could have that fire when the sheet is activated.

I was originally thinking of doing without VBA and having something along the lines of =MyList & "New" or exploding the range into a text string in someway in the validation dialog but wasn't sure how I would do this (if it was possible).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top