Preventing certain values from being input

[mweick]

I have column AW in a spreadsheet that I need certain values to not be allowed and a pop up message be displayed if one of these values is entered saying "Invalid Vendor Number"

The values are 025688, 022590, 023021, 024110, 027100, 090560, 090561, 001802

I first thought simple Data Validation would suffice but it's not possible, then I tried to construct a custom VBA with no luck and a large headache. Help is greatly appreciated on this one, thank you in advance.

 

[JLGWhiz]

Copy this procedure to the worksheet code module for the sheet on which you want to control the entries. To install the code, right click the sheet name tab for that sheet, then click 'View Code' in the pop up menu. Paste the code into the large pane of the VB editor and save your workbook as a macro enabled workbook (.xlsm), to preserve the code when the workbook closes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("AW:AW")) Is Nothing Then
        Select Case Target
            Case 25688, 22590, 23021, 24110, 90560, 90561, 1802
                Application.Undo
                MsgBox "Invalid Vendor Number", vbExclamation, "INVALID ENTRY"
        End Select
    End If
Application.EnableEvents = True
End Sub

Once installed, if the restricted numbers are entered by a user, they will be immediately cleared and a message will appear telling the user they made an invalid entry.

 

[mweick]

JLGWhiz,

Thank you so much, this worked like a charm and as expected. Huge thumbs up!

Thanks for the help on this, I was able to analyze this and learn what I was doing wrong. Fantastic!!

 

[JLGWhiz]

JLGWhiz,

Thank you so much, this worked like a charm and as expected. Huge thumbs up!

Thanks for the help on this, I was able to analyze this and learn what I was doing wrong. Fantastic!!

You're welcome,
Regards, JLG