Worksheet change event_event on based on null value

[punnu07]

Hi,

I have the following 4 columns in an excel file. In column Status I have different drop down values like Completed pending, to review etc.

Now my problem is if final review issued date is blank, then user shouldn't be able to select the status "Completed" from drop down in status column and a error message should pop up.

Final Review Issued

Risk Engineer

Underwriter

Status

<tbody>
</tbody>

Thanks in advance.

 

[Takae]

Please try this code.

Copy to sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then
        Exit Sub
   Else
        If Target = "" Then
            Application.EnableEvents = False
            With Cells(Target.row, 4).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:="pending,to review"
                .IgnoreBlank = True
                .InCellDropdown = True
                .IMEMode = xlIMEModeNoControl
                .ShowInput = True
                .ShowError = True
            End With
            MsgBox "Err msg"
        Else
            With Cells(Target.row, 4).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="Completed,pending,to review"
                .IgnoreBlank = True
                .InCellDropdown = True
                .IMEMode = xlIMEModeNoControl
                .ShowInput = True
                .ShowError = True
                End With
            End If
        End If
        Application.EnableEvents = True
End Sub

 

[punnu07]

It is not working and also I am not able to attach my file for your reference.

 

[Takae]

Nothing happened?
Did you find sheet module?

Right click on the tab name.
Select "View code(V)".
Copy the code to the sheet module.

 

[punnu07]

I have copied the code under Modules section but nothing is happening...

 

[Takae]

Where "Final Review Issued" is?
You type something in ColumnA("Final Review Issued"), same row in ColumnD has drop down list which has "Completed" and "pending" and "to review".
If you delete the value in ColumnA, drop down list of columnD has only "pending" and "to review".

 

[Sunny Kow]

Hi Punnu
Give this a try.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Coded by SunnyKow - 19/09/2016
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
      Dim ValList As String
      Application.EnableEvents = False
      On Error GoTo ErrorRoutine
        If Target = "" Then
          ValList = "Completed,Pending,Review"
        Else
          ValList = "Pending,Review"
        End If
      With Target.Offset(, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ValList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    End If
    Application.EnableEvents = True
    Exit Sub


ErrorRoutine:
    Application.EnableEvents = True


End Sub

 

[punnu07]

"Final Review Issued" is in column L (user enter date in this column) and "Status" is in column O (which has drop-down values as Completed, UW to review, Risk Engineer to review and so on)
Yes we enter date in this column and in the same row, we select options as per above.
Now the problem is some time user overlooked this "Final review issued" column and directly change the status to "Completed" which is incorrect. So we require validation that if user left the "Final review issued" column as blank then he/she can't be able to select the completed option in column "Status". Below is the column headings.

Insured

Status

Policy Number

Product Line Subtype

Date of Request

Type of Requests

Fee

Date Due

Renewal Date

Site Visit Date

Draft Review Issued

Final Review Issued

Risk Engineer

Underwriter

Status

Comments

Link

<tbody>
</tbody>

 

[Sunny Kow]

Then try this revised code.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Coded by SunnyKow - 19/09/2016
    If Not Intersect(Target, Range("[B][COLOR=#ff0000]L:L[/COLOR][/B]")) Is Nothing Then
      Dim ValList As String
      Application.EnableEvents = False
      On Error GoTo ErrorRoutine
        If Target <> "" Then
          ValList = "Completed,Pending,Review"
        Else
          'Clear cell if not date entered but status show Completed
          If Target.Offset(, 3).Value = "Completed" Then
            Target.Offset(, 3).Value = ""
          End If
          ValList = "Pending,Review"
        End If
      With Target.Offset(, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ValList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    End If
    Application.EnableEvents = True
    Exit Sub


ErrorRoutine:
    Application.EnableEvents = True


End Sub

 

[punnu07]

Not working.. Can you please do me a favour. please put this code in an excel file. Also, please note that "Status" column already has drop-down values.