Prevent filing of cells

[apgmin]

In column say column A
If have put a drop down list from which the values may be selected
If anyone types some thing the entry gets rejected, but if some one simply drags the value from above sells it works.
How do I prevent this and force the person to select from the drop down list

 

[DanteAmor]

I was doing some tests, and from the sheet, I didn't find any way.
You may have to protect the sheet and capture from a UserForm. With the code in the UserForm, you can Unprotect the sheet, put the data in the cell and protect the sheet again.

In this way there will be no possibility to modify the contents of the cell if it is not from the UserForm. Generally speaking, since the security issue is quite broad.

 

[Jaafar Tribak]

Or maybe execute an Undo command in the sheet change event.

 

[Marcelo Branco]

To prevent copy / paste into cells with validation rules, maybe something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValRange As Range, ValType As Long
    
    'Contains the cells with validation rules - adjust to suit
    Set ValRange = Range("A2:A5")
    
    If Not Intersect(Target, ValRange) Is Nothing Then
        On Error Resume Next
        ValType = ValRange.Validation.Type
        If Err.Number <> 0 Then
            Application.Undo
            MsgBox "Invalid operation - Select from the dropdown list"
        End If
        On Error GoTo 0
    End If
End Sub

M.

 

[DanteAmor]

To prevent copy / paste into cells with validation rules, maybe something like this

M.

It doesn't work for me, it enters a loop and Excel falls.
It does not allow me to put the values ​​of the validation list itself.

 

[Rick Rothstein]

It doesn't work for me, it enters a loop and Excel falls.
It does not allow me to put the values ​​of the validation list itself.

Maybe Application.EnableEvents needs to be turned off before the Application.Undo and turned back on afterwards?

 

[DanteAmor]

Maybe Application.EnableEvents needs to be turned off before the Application.Undo and turned back on afterwards?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValRange As Range, ValType As Long
    'Contains the cells with validation rules - adjust to suit
    Set ValRange = Range("A2:A5")
    If Not Intersect(Target, ValRange) Is Nothing Then
        On Error Resume Next
        ValType = ValRange.Validation.Type
        If Err.Number <> 0 Then
[COLOR=#0000ff]            Application.EnableEvents = False[/COLOR]
            Application.Undo
[COLOR=#0000ff]            Application.EnableEvents = True[/COLOR]
            MsgBox "Invalid operation - Select from the dropdown list"
        End If
        On Error GoTo 0
    End If
End Sub

Yes I tried, but the selection of the data from the validation list is deleted.

 

[Rick Rothstein]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValRange As Range, ValType As Long
    'Contains the cells with validation rules - adjust to suit
    Set ValRange = Range("A2:A5")
    If Not Intersect(Target, ValRange) Is Nothing Then
        On Error Resume Next
        ValType = ValRange.Validation.Type
        If Err.Number <> 0 Then
[COLOR=#0000ff]            Application.EnableEvents = False[/COLOR]
            Application.Undo
[COLOR=#0000ff]            Application.EnableEvents = True[/COLOR]
            MsgBox "Invalid operation - Select from the dropdown list"
        End If
        On Error GoTo 0
    End If
End Sub

Yes I tried, but the selection of the data from the validation list is deleted.

We don't know the details, so we cannot write the code, but personally, since VBA code is being used, I would remove the Validation and implement it in code.

 

[DanteAmor]

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValRange As Range, ValType As Long, rango As Range, f As Range
    'Contains the cells with validation rules - adjust to suit
    Set ValRange = Range("A2:A5")
    If Target.Value > 1 Then Exit Sub
    If Not Intersect(Target, ValRange) Is Nothing Then
        On Error Resume Next
        ValType = ValRange.Validation.Type
        Set rango = Range(Mid(Target.Validation.Formula1, 2))
        Set f = rango.Find(Target, , , xlWhole)
        If f Is Nothing Then
          If Err.Number <> 0 Then
              Application.EnableEvents = False
              Application.Undo
              Application.EnableEvents = True
              MsgBox "Invalid operation - Select from the dropdown list"
          End If
        End If
        On Error GoTo 0
    End If
End Sub

 

[Marcelo Branco]

It doesn't work for me, it enters a loop and Excel falls.
It does not allow me to put the values ​​of the validation list itself.

Dante,

The code worked perfectly for me - i used a List as validation rule, if it matters.

In fact, it's an old and well known code i saw, some years ago, in a book.
(well, as it is getting older it may not be able to function anymore )

About your comment:
"It does not allow me to put the values ​​of the validation list itself"
Yes, this is exactly the goal, not to allow the user to paste any value, even valid ones, as this would destroy the validation rules. By allowing, the code would have to redo the rules for future entries - i'm not sure if this is the right way to do things

I'm curious: in which scenario it didn't work for you.

M.