not allowing certain value to be entered

[lezawang]

Hi there
I have a sheet which has many columns. in one column, I have values as below. I just want to create a rule/pop up window etc if user enter A-31 or A-24B then excel will say no you can not enter these values. Any hint/idea would be very much appreciate it. Thank you.

A-33

A-27

A-31

A-23

A-45

A-24B

<tbody>
</tbody>

 

[Logit]

.
This is one method :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LastRow As Long
    Dim i As Long
      'LastRow = Range("B" & Rows.Count).End(xlUp).Row
      For i = 2 To 15
        Select Case Range("A" & i)
          Case "A-31"
            MsgBox "A-31 and A-24B are not permitted entries in this range." & vbCrLf & vbCrLf & _
                "Please select a different entry.", vbCritical, "Entry Error !"
                Exit Sub
          Case "A-24B"
            MsgBox "A-31 and A-24B are not permitted entries in this range." & vbCrLf & vbCrLf & _
                "Please select a different entry.", vbCritical, "Entry Error !"          'Case ""
                Exit Sub
        End Select
      Next i
End Sub

 

[jtakw]

Hi,

Select/highlight the first cell in subject column, select down as far as needed.

Use this as Custom DV formula (Change F1 to first cell of your subject column):

=AND(F1<>"A-31",F1<>"A-24B")

You can also add "input message", and/or "error alert" within the DV input box.