List Validation

R

Rajendra

Hello Everybody,

I have applied list validations to cell, say C4.

The list contains two values : CY & CFS

But when I copy & paste special values other than the list (sa
WAREHOUSE from cell B4), excel accepts the same.

Can anyone help me to restrict the cell updation only as per
list.

Thnks & Rgds,
Rajendra
 
F

Frank Kabel

Hi
with Data - Validation you can't prevent copy/paste
operations. This would require VBA
 
J

JE McGimpsey

Since Validation can be pasted over (and therefore removed), you'll need
to use an event macro.

Put this in your workksheet code module (right-click the sheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C4")) Is Nothing Then Exit Sub
With Range("C4")
If Not IsEmpty(.Value) Then
If Not ((.Value = "CY") Or (.Value = "CFS")) Then
.ClearContents
.Activate
MsgBox _
"Cell C4 may only have the values ""CY"" or ""CFS"""
End If
End If
End With
End Sub
 
R

Rajendra

Hi JE McGimpsey,

Thnks for yr quick help, it worked!

But I want to be be little more greedy!

Now the real problem is I want to apply these validations to
all cells ranging from C4 to C107.

Appreciate your help.

(Thnks to Frank Kabel also for advice)

Thnks & Rgds,
Rajendra
 
J

JE McGimpsey

Then I'd modify it like this:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("C4:C107")) Is Nothing Then
If Not IsEmpty(.Value) Then
If Not ((.Value = "CY") Or (.Value = "CFS")) Then
.ClearContents
.Activate
MsgBox "Cell " & .Address(False, False) & _
" may only have the values ""CY"" or ""CFS"""
End If
End If
End If
End With
End Sub
 
R

Rajendra

Hi,

Thats the solution I was looking for!

Thnks a million for the same.

Rgds,
Rajendra
 
Top