Enforce users to select value from drop-down list

Question

I'm trying to make sure that users are selecting values in a column from a drop-down list. The list is in a different page. Also if the users are overwriting the drop-down list using "PASTE", then I need to pop-up a message box warning them about the same.

How can I achieve this using VBA Macro

Best Regards,

gmainak

Answer 1

You need to first have a Validation set up. In case people paste and thus surpass your security measure you need to resort to more though solutions:
I created a simple function that can check against each cell in Target range on a Worksheet_Change event.
DISCLAIMER: Do note we are not a coding service, if you do not understand these terms we are not to blame; in this case you need to read more on the basics.

Function isValid(rng As Range) As Boolean
'isValid = rng.Validation.Formula1
Dim str As String, formstr As String, rng2 As Range, rng3 As Range
str = rng.Validation.Formula1
'str = Replace(str, "INDIREKT", "INDIRECT") 'hungarian specific
Set rng2 = Evaluate(str)
For Each rng3 In rng2
    If rng3 = rng Then
        isValid = True
        Exit Function
    End If
Next rng3
End Function

Answer 2

place this code in dropdown worksheet code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = $A$1 Then '<--| change $A$1 to actual address of your dropdown cell
        If Not Application.CutCopyMode = False Then
            MsgBox "Warning !!" & vbCrLf & vbCrLf & "You are NOT allowed to paste over the drop-down list"
            Application.CutCopyMode = False
        End If
    End If
End Sub

Answer 3

Like Tjb1 says,

Use data validation ( found in the data tab- allow - list )to lock the list to your selection then hide the tab or lock the tab with the list in.

Through data validation you can add input message or error alert to inform the user.