How to restrict data entry in cells based on some range of values entered in another corresponding cell RRS feed

  • Question

  • HI ,

    I have huge amount of 3 digit codes in Column A. Only 16 codes should have access to data entry in respective cell in column B and all other cells corresponding to codes other than 16 codes should not accept any data in the respective cells in Column B.

    What are the solutions

    Immediate help would be appreciated.

    Friday, January 29, 2016 6:02 PM

All replies

  • Create a named range of your 16 allowed codes named "Allowed"  (without the quotes) to allow the VBA code to check for valid codes in column A

        1) Copy this code.
        2) Right-Click the sheet tab of interest.
        3) Select "View Code"
        4) Paste the code into the window that appears.
        5) Save the file as a macro-enabled .xlsm file.
        6) Make changes as needed

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim c As Range

        If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub  

        'Turn off events to keep out of loops
        Application.EnableEvents = False

        For Each c In Target

            Dim v As Variant
            v = Application.Match(c.Offset(0, -1).Value, Range("Allowed"), False)
            If IsError(v) Then
                MsgBox "Don't even think of doing that!"
            End If

        Next c

        'Turn events back on to get ready for the next change
        Application.EnableEvents = True
    End Sub

    • Proposed as answer by ryguy72 Saturday, February 6, 2016 7:23 PM
    Friday, January 29, 2016 8:29 PM
  • An option will be to use Data Validation from excel.

    Thanks in advance, Ciprian LUPU

    • Proposed as answer by ryguy72 Saturday, February 6, 2016 7:23 PM
    Sunday, January 31, 2016 7:39 AM