After error message, return to original cell

[jrota]

hello,

Enter date cell is H2. If user tries to select and enter info in other cells before entering value in H2 (date) I would like error message to occur (which it does) but have excel return to cell H2 (which it does not).

Here is the code I have so far.

Private Sub Worksheet_Change(ByVal Target As Range)

'ENTER DATE
If Range("H2") = "" Then
MsgBox "Please enter DATE first.", vbCritical, "Error Data Entry"
Exit Sub
End If

JR

[jrota]

Also, I only want this to occur until DATE is entered into Cell H2. After DATE entered, user can move on to other cells in Workbook

[mjr veverka]

Please Login or Register  to view this content.

[jrota]

That works. Thank you.

bug: When user enters anything but date (example - as) error message occurs "Run-Time Error '1004' Cannot change part of a merged cell"

Johny

[jrota]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("H2")) Is Nothing Then
If Range("H2") = "" Then
Target.ClearContents
Range("H2").Select
MsgBox "Please enter DATE first.", vbCritical, "Error Data Entry"
End If
Else
If Target.Value <> "" Then
If Not IsDate(Target.Value) Then
Target.ClearContents
Target.Select
MsgBox "Not a DATE", vbCritical, "Error Date Entry"
End If
End If
End If
Application.EnableEvents = True

*** Target.ClearContents is displayed with Yellow Highlight over it

[jrota]

appears cells H2 to L2 are merged. That is what is causing the issue. Maybe by substituting H2 with H2:L2?

Johny

[jrota]

thats ok. I just un-merged the cells. this is solved

[mjr veverka]

For merged cells try this:

Please Login or Register  to view this content.

[jrota]

Hello, If I wanted to add, user must enter date AND "NAME" to this code whereas N2 (merged cells) is Name cell, how would I do that? Error message to user changes to "Please enter DATE and NAME first".