Denied Entry Dependent Upon the Value

user_6090984 0
Viewed 68

I would like to ask that if the "Denied" word is enter in this Range("D2:D500") then values after it's row cannot be entered only in Column E.

wherever "Denied" word enter to Column D then only below cells at Column E don’t have right to enter any value, where else user can enter the values at Column A, B, C and D only. If user enter the value in Column E then display the message "You are not permissible" and clear the content of that cell.

For the Same row the following code I am using is given below:

' this code for Column D which is made for clear data in the same row

If rngTarg Is Nothing Then GoTo ExitHandler
For Each rngToChk In rngTarg
    Select Case rngToChk.Value
    Case "Denied"
        rngToChk.Offset(0, -1).ClearContents
        rngToChk.Offset(0, -2).ClearContents
        rngToChk.Offset(0, 1).ClearContents
        rngToChk.Offset(0, 2).ClearContents
    End Select
Next rngToChk


' this code for Column E which will help to user that it won’t have access to enter data in the same row

For Each cell In rngTarg
    If cell.Offset(0, -1) = "Denied" Then
    cell.Select
    MsgBox Excel.Application.UserName & ", As you enter <" & cell.Offset(0, -1) & "> in Column D" _
        & vbNewLine & "That is why you cannot enter Value.", vbokOnly + vbInformation, "Information"

        cell.ClearContents
        cell.Offset(1, 0).Select
        GoTo ExitHandler
    End If
Next cell

I hope it will help to understand my query.

Thanks & Regards

Muneeb

enter image description here

vba excel
2 Answers

No need to use VBA. You can use Data Validation

  1. Select Range E2:E LastCell. In Excel 2007+, it isE2:E1048576. For older version it is E2:E65536. Simply Select cell E2 and press Shift + End + Down Arrow

  2. Click on Data Tab and then click on Data Validation

  3. Select Custom and in the Formula type =COUNTIF($D$1:$D1,"Denied")=0 as shown below

    enter image description here enter image description here

  4. Click Ok and we are done

In Action

enter image description here

user_11405790

Install the code below in the code sheet of the worksheet on which you want entries to be denied. This location is vital. In another location the code won't run automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 304

    Const Denied    As String = "denied"        ' change to suit (not case sensitive)
    Const DeniedClm As String = "D"             ' change to suit: no modifications to the right of this column
    
    Dim Fnd         As Range                    ' cell where Denied is found (if any)
    Dim Ct          As Long                     ' numeric equivalent of DeniedClm
    
    Ct = Columns(DeniedClm).Column
    With Target
        If .Column > Ct Then
            Set Fnd = Columns(Ct).Find(Denied, , LookIn:=xlValues, _
                                       LookAt:=xlWhole, MatchCase:=False)
            If Not Fnd Is Nothing Then
                If .Row >= Fnd.Row Then
                    MsgBox "This cell shall not be modified." & vbCr & _
                           "Your change will be rolled back.", _
                           vbInformation, "Modification not allowed"
                    With Application
                        .EnableEvents = False
                        .Undo
                        .EnableEvents = True
                    End With
                End If
            End If
        End If
    End With
End Sub

You can change the word "denied", in case you need it in another language, and you can change the column in which it may appear. The function will take no action if there is no such word - in a cell by itself - in the designated column.

user_76754370
Related