Making a cell mandatory based on previous cell entry

Question

I am looking to achieve the following:

Column H has 5 potential inputs from a data validation list:

Pass, High, Medium, Low & N/A

If Column H is either High, Medium or Low, I need Column I to immediately demand a comment by way of a dialogue box.

How can I set this up?

It may also be beneficial to add that I know nothing about script/code writing nor do I understand any "Excel jargon", I am a pretty basic user & everything I know is self taught using trial & error!

Solution

Based on my understanding of your question, I suggest a VBA Solution. In this example the sample column range is H2:H7 and your intended Validation is already in place for this range.

In your worksheet, press ALT + F11 to access VBA Editor. Under Left Pane see Microsoft Excel Objects. Below that double click the intended worksheet to open the Even Driven programming window. From the first drop down select Worksheet then from the second drop down select Change. This is Worksheet Change Event. The following lines will be displayed in the code editor.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Put the following code between these two lines.

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
    Exit Sub
End If

Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("H2:H7"))
If isect Is Nothing Then

Else

 If Target.Value = "High" Or Target.Value = "Medium" Or Target.Value = "Low" Then
     com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)

     Do While comm1 = ""
        comm1 = Application.InputBox(prompt:=com, Type:=2)
        On Error GoTo myloop
        If comm1 = False Then
            comm1 = ""
        End If
myloop:
     On Error GoTo -1
     Loop
     Target.Offset(0, 1).Value = comm1

 Else
    Target.Offset(0, 1).Value = ""  'Remove this line if not desired
 End If

End If

Save the file as Macro Enabled Excel workbook. Back in the worksheet. Test this code by selecting the intended values from the Validation List Box and see if it works for you and meets your requirment. This code may not have extensive error checking though.

One small thing I have added is later if you change the status to Pass the comment is removed from the column I.

Comments

[LPChip]

Data validation may be possible for this.

[LPChip]

Data validation does not appear to work for this. You can make it popup and tell the user that column I needs to be filled in as well, but in order to not get that error, they would first need to fill in column I, and that would work weird from a user's perspective. So a Macro does indeed seem to be the only way to go.

[LPChip]

It is possible using conditional formatting to make the cell in Column I change color if they haven't entered anything, but its up to the user to actually do it.

[Wozza001]

Thanks for answers but I do need an error to show to prompt the auditor to complete a comment. I am useless when it comes to writing scripts/codes for macros.

[LPChip]

Please understand, SuperUser is not a "Please write me a script kind of service." If you are unable to provide a macro, then do some research first.

[patkim]

Do you want to enter the comment in Cells of column I or in the Comment Box (Generally the yellow comment box) as thrown at a cell?

[Wozza001]

I want the comments to be in the cells of column I

[Rajesh Sinha]

@Wozza001,, since you are unable to handle VBA Script then the best & easiest is IF Else Logic in adjacent cell !

[Wozza001]

Thank you for this, although for some reason, it's not working - I have changed the range from H2:H7 to H17 but no joy. Maybe I am saving it incorrectly?

[patkim]

What version of Excel you have?

[Wozza001]

Excel 2007.....

[patkim]

Have you saved the file as Macro Enabled workbook xlsm?

[Wozza001]

Yes its saved as macro enabled

[patkim]

Open the file and recheck if the code exists in the file. What error you get if any? What are the Macro security settings set in your version of Excel? Macros may have been disabled in Trust Center.

[Wozza001]

that's working fine! thank you so much! is there any way possible to enable an error box to prompt a comment directly into I17 instead of a box which the comment is typed into?

[patkim]

A message box to prompt a comment in cell say I7 is possible but there's no assurance that user actually entered value in I7. User may just skip it thereafter. Forcing a user to enter into a specific cell may be possible but would require a different logic.

[Wozza001]

Ok, if it is possible to prompt an error box & to ensure that a user adds a comment, can it be made so that a print button (which I already have added to the sheet) can only be used once a document has been saved, but it can only be saved when all mandatory comments have been added?