Force data entry if condition is met [SOLVED]

[Shaun]

Hi All


I have a list of "jobs" to be completed, as the work comes in I enter the date the work came in (column O), from there the work is allocated out to various people. What I would like to be able to do is if I enter a date in say cell O3 "07/08/2013" (August 7) I would like Excel to force me to enter a persons initials in cell D3.


I am open to any method for doing this, formula/VBA, but I am not sure how to do it. This will eventually tie into automated email sending scenario if work has been in for >14 days and a completed date has not been enetered.


Thank you in advance


Cheers


Shaun

 

[Luke M]

If you're willing to switch the order of things, the simplest solution would be to use Data Validation to check if initials have been entered in D3. So, with O3 selected, Data Validation - Custom. Formula is:

=LEN(D3)>=2


Uncheck the box for "ignore blank"

If you want, you can customize the input message and error alert message. Workbook will not prevent you from editing O3 unless D3 has been filled in.


Not the exact solution you described, but perhaps...?

 

[SirJB7]

Hi, Shaun!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Force%20data%20entry%20if%20condition%20is%20met%20%28for%20Shaun%20at%20chandoo.org%29.xlsm


Forcing that condition will be difficult to keep it on after entering data in other cells after doing so at column O, but doing it once could be handled by this code to be placed in the class module of the related worksheet. You could also apply a CF condition for column D to reinforce the prompt request. Otherwise you should consider VBA code to be manually run from a command button click event and check the whole entries.


-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const kiTarget = 15
Const kiSource = 4
Const kvDefault As Variant = "XXX"
' declarations
' start
If Application.Intersect(Target, Columns(kiTarget)) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = True
' process
With Cells(Target.Row, kiSource)
If IsDate(Target.Cells(1, 1).Value) Then
If .Value = "" Then
.Value = kvDefault
.Select
MsgBox "Input required in selected cell", _
vbApplicationModal + vbCritical + vbOKOnly, "Error"
End If
End If
End With
' end
Application.EnableEvents = True
End Sub

[/pre]
-----


Just advise if any issue.


Regards!

 

[Shaun]

Hi Luke M and SirJB7


Thank you both for your solutions to the problem.


This problem has been successfully solved.


Again thank you Luke M and SirJB7


Cheers


Shaun

 

[SirJB7]

Hi, Shaun!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!