Protect cells that has data Validation

Nigel.

List style data validation in Dashboard cells E4 and E5 will not allow users to paste values (other than those defined by the List) but the cells could be overwirtten if the entire cell C5 say is pasted. To prevent this, you can add an event macro behind Sheet1 (Dashboard") as follows, with comments and disabling line in bold:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Ignore if user is not picking Field cells...
If Intersect(Target, Range("E4:E5")) Is Nothing Then Exit Sub
'Otherwise, prevent paste
Application.CutCopyMode = False

End Sub

Now, if the user copies C5, choosing E4 or E5 will stop any pasting. 

In the revised file attached, sheet Jan has been reduced from 100,000 rows to 200  (to reduce size only). In the Dashboard sheet, cells C4:C5 and E4:E5 re unlocked as follows:

1. Unlock using ribbon Review /Unprotect Sheet and provide password
2. Use Ctrl_left click to select cells
3. Right click/Format cells.../ Protection tab then uncheck "Locked" then Okay. Re-protect sheet using ribbon Review /Protect Sheet  (and uncheck "Locked cells")

Then the user can select only the mentioned cells in the sheet (shaded pale green) or tab between them. They can press the Search and Clear buttons.

In VBA, we can then provide the password to unlock the sheet so we can change cells (then re-lock it after the macro has run). In the main macro extract below, the lines in bold are added (including a line to state report results in B8):

Sub Data_Search()
' TeachExcel.com

Dim ws As Worksheet
Dim dashboard As Worksheet

.
.
.<< code unchanged>>
.
.

'Clear Dashboard
Call Clear_Data
'Unlock sheet
dashboard.Unprotect Password:="123"
.
.
.<< code unchanged>>
.
.
Next ws
'state report basis
dashboard.Range("B8").Value = "Search results " & _
Now & " for (" & _
FieldValue & "=" & searchValue & _
" OR " & FieldValue2 & "=" & searchValue2 & ")"

' reapply protection
dashboard.Protect Password:="123"

'Application.ScreenUpdating = True 'Turn it back on at the end of the macro!

End Sub

Note that the .Unprotect method is applied to sheet AFTER the Clear_Data macro since that too needs the same lines (and also now clears the search values) as follows (additions in bold):

Sub Clear_Data()

'Dashboard sheet
Set dashboard = Sheets("Dashboard")
'clear search values and reoprt basis
dashboard.Range("C4:C5").ClearContents
dashboard.Range("B8").ClearContents

dashboard.Unprotect Password:="123"

'Data table information
dashboardDataColumnStart = 3 ' Column for the data on the dashboard
dashboardDataRowStart = 10

dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).ClearContents
dashboard.Protect Password:="123"


End Sub

That's it! 

Users can see your password in the code if they know VBA (but you could choose to protect the VBA Project and add extra code to lock/unlock).

Hope this is right for you.