MrExcel Publishing
Your One Stop for Excel Tips & Solutions

List Validation...


Posted by Muazzam on January 23, 2001 12:25 PM

Hi

I have a created a pulldown list say: a,b,c,d.

Now when I copied a value say "bcd" from another cell.
I have pasted in the cell containing the pulldown list.

Since the value pasted 'bcd', is not within the range of the
pulldown, an error message should be popped up.

But by doing copying and then pasting the validation in the
pull down list is not taking place.

Please Help.
TIA
Muazzam


Posted by Mark W. on January 23, 2001 1:38 PM

See the Excel Help topic for "data validation" and
then "Specify the valid entries for cells". It tells
you how to use the Data Validation... menu command.

Posted by Dave Hawley on January 23, 2001 8:30 PM

Hi Muazzam

Seems Mark has go this wrong :O)))

Validation does NOT prevent users from pasting in values outside of the set Validation range, to achieve this you will need VBA.


Right click on your sheet name tab and select "View Code", paste this code over the top of what you see.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Textfind As String
If Target.Address = "$E$4" Then
On Error Resume Next
Textfind = Range("A1:A20").Find _
(What:=Target, After:=Range("A1:A20").Cells(1, 1))
If Textfind = "" Then
MsgBox "Sorry, not within the list"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End Sub


Where "$E$4" is the cell with your list of valid entries. Range("A1:A20") is your range containing your List. So change as needed


Push Alt+Q to return to Excel and Save.

Hope this helps
Dave
OzGrid Business Applications

Posted by Bob on January 24, 2001 12:19 AM

Where "$E$4" is the cell with your list of valid entries. Range("A1:A20") is your range containing your List. So change as needed Push Alt+Q to return to Excel and Save. Hope this helps Dave


Sorry..I try this code..but its not working..
what I am doing wrong..plz help Dave..

Posted by Lokman Mat Lazim on January 24, 2001 12:35 AM

Hope this helps you.....

Sub List()
Range("$E$4").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$1:$A$20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


ps : but you have to protect the sheet first

Posted by Dave Hawley on January 24, 2001 3:32 AM

Hi Bob

It does work, but you must put it in the Sheet module of the sheet you now have your Validation cell on. I.E. Right click on your sheet name tab and select "View Code".


Change: "$E$4" to the cell you have Validated

Change: Range("A1:A20") To a range that has all allowed entries.


If You are still having problems E-Mail me and I'll send you an example.

Dave

OzGrid Business Applications

Posted by Bob on January 24, 2001 6:00 AM

Dave Hawley..plz read inside....

i have followed all your insruction but still
can't do it..i've also paste the code in the worksheet
change in the VBA..but still can't do it..if you
have spare time..would you sent me the workbook
that you've already do and succeed to do this job..
thanks

Hi Bob It does work, but you must put it in the Sheet module of the sheet you now have your Validation cell on. I.E. Right click on your sheet name tab and select "View Code". Change: "$E$4" to the cell you have Validated Change: Range("A1:A20") To a range that has all allowed entries. If You are still having problems E-Mail me and I'll send you an example. Dave

Posted by Mark W. on January 24, 2001 7:52 AM

touché

Posted by Dave Hawley on January 24, 2001 9:02 PM

Sorry mark, couldn't resist. Glad to see your not as volatile as me. Keep up the good work.


Dave

OzGrid Business Applications

Posted by Bob on January 25, 2001 5:29 AM

Thanks Dave. you are a doll...

can't do it..i've also paste the code in the worksheet change in the VBA..but still can't do it..if you have spare time..would you sent me the workbook that you've already do and succeed to do this job.. thanks

Posted by Muazzam on January 25, 2001 8:53 AM

Thanks one and everyone