Mandatory Cells based on other column

[lbutton]

Hi All -

I am trying to make cells in a column mandatory based on the input of a different column.

If the user enters any input at all in column I, then the user must choose from the dropdown menu in column K before saving. However, if there is no input in column I, then nothing would be mandatory in column K.

For example, if the user puts, "Need to purchase new computer" in I6, then in K6, they need to be required to choose something from the dropdown already in place.

Thanks for your help!

I am using Excel 2010

 

[tonyyy]

You might give this a try...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim r As Range
Dim LastRow As Long

LastRow = Cells(Rows.Count, "I").End(xlUp).Row

For Each r In Range("I1:I" & LastRow)
    If r.Value <> "" And r.Offset(0, 2).Value = "" Then
        MsgBox "Please make a selection in Column K, Row " & r.Row
        Cancel = True
    End If
Next r

End Sub

Place the above code in the ThisWorkbook module, not a standard vba module.

Cheers,

tonyyy

 

[lbutton]

Thank you so much tonyyy!

It works great! :D

 

[lbutton]

tonyyy -

I saved the excel as an Excel Macro-Enabled Workbook. When I closed it and then re-open it, at the top of the spreadsheet it has a message that says, "Security Warning Macros have been disabled." And then there is a button that says, "Enable Content". If the button is pressed, then everything works as it should, however if it is not pressed, people can type in column I, not make a selection in column K and save. I am afraid the users of this spreadsheet will not hit "Enable Content" before using.

Any thoughts?

Thanks!

 

[tonyyy]

VBA can only do so much, and forcing users to enable content isn't one of them.

The best advice is to request, persuade, cajole, convince, bribe, demand, mandate, pout or somehow entice your users to enable macros and content.

Good luck!

tonyyy

 

[lbutton]

Thanks for your help tonyyy!