Do not allow any value apart from data Validation values in particular cells

[Abhijeet]

Hi

I have data i want to avoid data validation range any other values in those cells i use macro for disable copy paste but still those cells user are pasted other values so please tell me how to restrict users use only data validation list values in cells

 

[vletm]

@Abhijeet
Set always cell's Data Validation then user activate it.
Then no matter, if still someone pastes over it.

 

[Abhijeet]

yes Data Validation no matter but i want that data i am copy paste each file data so i need then data i am not look that cell is Data Validation i want data so i need correct values please tell me how to do this

 

[Abhijeet]

Hi

In attach file Sheet1 has disable paste but i want only this Range("B9:B1508,G9:G1508,J9:J1508") disable but in this file is disable any value insert in Column B:J please tell me only particular range how to do this

 

[vletm]

@Abhijeet
If I tried to understand Your case ...
You would try to test next this time:
1) Make Three new columns to RIGHT side of You table, like BA, BB & BC
2) Those new columns do same feature than columns B, G & J now.
3) BA9 = B9, BB = G9 & BC = J ( = only links from new columns)
4) protect those B, G & J as NOBODY cannot touch
5) Only some users could modify columns BA, BB & BC or normally those should be hidden (of course those new 'masters' could be in another new sheet too)
I couldn't test this idea ... cause some files setting.
> Try to get an idea ...

 

[Abhijeet]

This is not i want each user has his own count sheet each user fill but few are copy paste data on data validation cells so correct values we can not picked so i want block paste on data validation cells

 

[vletm]

I don't have more ideas than:
1) Prevent those users to use file ... any files
2) Backups after every change
3) Tell the Boss or Boss's Boss - they loose money

 

[Abhijeet]

Hi

I found Code but please tell me i have 31 sheets i want to run macro on each sheet so please tell me how to do this

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
'  Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[Abhijeet]

This Macro Work Perfect where ever Data Validation Cells only If Copy From Different Work Book then not work
So Can U please tell me how to do this

Dim fromBreak As Boolean, x As Integer, selChangeAddress As String, dragStatus As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = dragStatus
End Sub

Private Sub Workbook_Open()
dragStatus = Application.CellDragAndDrop
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
y = 0
On Error Resume Next
y = Target.Validation.Type
If x <> y And fromBreak = False And selChangeAddress = Target.Address Then
fromBreak = True
Application.Undo
fromBreak = False
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
x = 0
On Error Resume Next
selChangeAddress = Target.Address
x = Target.Validation.Type
End Sub