List Validation...

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.

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

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..

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

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

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

touché

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

Dave

OzGrid Business Applications

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

Thanks one and everyone