Prevent filing of cells

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
150
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
To prevent copy / paste into cells with validation rules, maybe something like this


Code:
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
Maybe Application.EnableEvents needs to be turned off before the Application.Undo and turned back on afterwards?

Code:
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.
 
Upvote 0
Code:
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.
 
Upvote 0
How about:

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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