message window with if function

[Chris]

Hello,

I have a worksheet with the date in the 1st column and other things like
name, client type etc in the following columns.
I wonder whether it would be possible to get a message window saying 'Please
fill in the date' if the user fills in the name etc without filling in the
date?
Sort of checking if the date has been filled in when starting of after
typing the name.

Does my explanation makes sense?

Thank you for your answers.

Chris

 

[Pete_UK]

You could put a formula like this in a helper column:

=IF(AND(B2<>"",A2=""),"Please fill in the date","")

Assumes that name is in column B and date in column A beginning on row
2 - adjust references as necessary, maybe give it a red emboldened
font, then copy down.

Hope this helps.

Pete

 

[Gord Dibben]

You could use sheet event code to enter the date/time in column A if user
entered anything in any other column.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column <> 1 Then
n = Target.Row
If Target.Value <> "" And Me.Range("A" & n).Value = "" Then
Me.Range("A" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Copy/paste the above to your worksheet module by right-click on sheet tab and
"View Code"

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Chris]

Gord,

thank you for your reply. It works fine, only is there a way to get the date
(other than today's date) typed in manually instead?
The worksheet will be filled in once a week, so the past dates are needed (I
should have mentioned this before, sorry).

Chris

 

[Gord Dibben]

You can type a date in manually in column A

That date won't update when you type in another column.

This statement If Target.Value <> "" And Me.Range("A" & n).Value = ""

says if Column A in Target row is already filled, don't run the event


Gord

 

[Chris]

Thanks, Gord.

Chris

You can type a date in manually in column A

That date won't update when you type in another column.

This statement If Target.Value <> "" And Me.Range("A" & n).Value = ""

says if Column A in Target row is already filled, don't run the event


Gord