Obligatory cells

[mgmugica]

Hello All,

I am working in a format where users will input the information, but I need to find a way to make a cell obligatory if there is information in a related cell.

For example, if in cell B there is a camp called quantity received, and I need to make cell A obligatory, which is a date of when they received it, if I have a value greater than 0.

I hope someone can help me.

 

[Ruddles]

At its very simplest, this code should do what you want:-

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Column <> 2 Then Exit Sub
  
  If Not IsEmpty(Target) And IsEmpty(Target.Offset(0, -1)) Then
    MsgBox "You must enter a date in A" & Target.Row & "!" & Space(5), vbOKOnly + vbExclamation
  End If
 
End Sub

Place it in the code module for the worksheet by right-clicking the worksheet tab and selecting View Code, then pasting the code in the code window.

Note that it will only remind the user once that he needs to enter the date: if he decides to ignore the message, then he won't be warned again (unless he changes the cell in column B again).

 

[mgmugica]

Rudless,

Thank you for your fast response, I am sorry, this is the first time I work with VBA and I am not sure how to personaliza the information, the way my Excel Spreadsheet is built and what I neet to check is as follow:

Quantity Received in Column P, and if this one is bigger than 0, then I need a date in Column N Last Paid Price.

Thanks for all your help!

Maria

 

[Ruddles]

[FONT=Courier New]Option Explicit[/FONT]
 
[FONT=Courier New]Private Sub Worksheet_Change([COLOR=black]ByVal Target As Range)[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=black]  If Target.Cells.Count > 1 Then Exit Sub[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]  If Target.Column <> [COLOR=red][B]16[/B][/COLOR] Then Exit Sub [/COLOR][COLOR=green]' stop if not column [/COLOR][COLOR=red][B]P[/B][/COLOR][/FONT]
 
[FONT=Courier New]  ' check [COLOR=blue][B]this cell[/B][/COLOR] and the cell two [COLOR=magenta][B]columns to the left[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=black]  If Not IsEmpty([COLOR=blue][B]Target[/B][/COLOR]) And IsEmpty([COLOR=magenta][B]Target.Offset(0, -2)[/B][/COLOR]) Then[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    If Target.Value > 0 Then[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]      MsgBox "You must enter a date[/COLOR] in N" & Target.Row & "!" & Space(5), vbOKOnly + vbExclamation, "Warning!"[/FONT]
[FONT=Courier New]    End If[/FONT]
[FONT=Courier New]  End If[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

 

[mgmugica]

You are a lifesave, thank you so much!

 

[Ruddles]

De nada!