Solved: Using an If statement on change event procedure

[bananatang]

Hi,

I cant seem to get the code working correctly regarding an if statement on a change event procedure.

Summary.

I have a cell U13 which has a default text "Insert Date" in it.

The conditions i wish to apply to this cell is

If the user deletes the text and leaves the cell blank or inserts some other text. I want a msgbox to notify the user that this cell cannot be left blank etc, and as a result will return the text "Insert Date" back into the cell.

OR

if the user inserts a date value to the cell (U13) to check if cell (BX13) value matches cell BM39, if so notify user via msgbox that the data matches.

OR

if the user inserts a date value to the cell (BM39) to check if cell (BX13) value does not match cell BM39, if so notify user via msgbox that the data does not match.

The code i have does not work when put all together. I would appreciate some help to refine the code.

My code is as follows

[vba]Private Sub Worksheet_Change(ByVal Target As Range)

If IsDate(Cells(13, 21)) Then
If Range("BX13").Value = Range("BM39").Value Then
MsgBox " The Pupil will be recorded as now being ""OFF ROLL""" & Chr(13) _
& Chr(13) _
& "The attendances codes logged for this student matches the duration" & Chr(13) _
& "the pupil has been at the IEC" & Chr(13) _
& Chr(13) _
& "Well Done !!!!!!", vbInformation, "Pupil now designated as being Off Roll"

Else

If Range("BX13").Value <> Range("BM39").Value Then
MsgBox "STOP !!! STOP !!!! STOP !!!!!" & Chr(13) _
& Chr(13) _
& " You have not recorded all Attendance Sessions for the period" & Chr(13) _
& " the pupil has been on roll....." & Chr(13) _
& Chr(13) _
& "Please ensure an Attendance Code is logged for each day the pupil" & Chr(13) _
& "has been On Roll" & Chr(13) _
& Chr(13) _
& "Thank You !!!!!", vbCritical, "Attendance Sessions Conflict"

Else

If Not IsDate(Cells(13, 21)) Then
If Range("U13") <> "Insert Date" Then
MsgBox "This cell cannot be left blank" & Chr(13) _
& "This Cell will return to its original value", vbCritical, "Blank Cell is not permitted"
Range("U13").Select
ActiveCell.FormulaR1C1 = "Insert Date"


End If
End If
End If
End If
End If

End Sub[/vba]

Thank

BT

[GTO]

Under the second OR, is this part a typo:

"if the user inserts a date value to the cell (BM39) to check if cell (BX13) value does not match cell BM39, if so notify user via msgbox that the data does not match."

... and is supposed to be...

"if the user inserts a date value to the cell (U13) to check... if cell (BX13) value does not match..."

[bananatang]

Under the second OR, is this part a typo:

"if the user inserts a date value to the cell (BM39) to check if cell (BX13) value does not match cell BM39, if so notify user via msgbox that the data does not match."

... and is supposed to be...

"if the user inserts a date value to the cell (U13) to check... if cell (BX13) value does not match..."

HI GTO,

Yes, your absolutely correct. It was a typo. My faults, apologies for that. The 2nd OR should ready
if the user inserts a date value to the cell (U13) to check if cell (BX13) value does not match cell BM39, if so notify user via msgbox that the data does not match."

Thank GTO

[GTO]

Hi BT,

Try:
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
'// Rather than having full test run ea time any cell changes, limit to when //
'// U13 is changed. //
If Not Application.Intersect(Target, Range("U13")) Is Nothing _
And Not Target.Count > 1 Then
If Not IsDate(Cells(13, 21).Value) Then
MsgBox "This cell cannot be left blank" & vbCrLf & _
"This Cell will return to its original value", _
vbCritical, "Blank Cell is not permitted"
'// Kill App events for a moment, or we will recurse//
Application.EnableEvents = False
Range("U13").Value = "Insert Date"
Application.EnableEvents = True
Range("U13").Select
ElseIf Range("BX13").Value = Range("BM39").Value Then
MsgBox " The Pupil will be recorded as now being ""OFF ROLL""" & Chr(13) _
& Chr(13) _
& "The attendances codes logged for this student matches the duration" & Chr(13) _
& "the pupil has been at the IEC" & Chr(13) _
& Chr(13) _
& "Well Done !!!!!!", vbInformation, "Pupil now designated as being Off Roll"
Else
MsgBox "STOP !!! STOP !!!! STOP !!!!!" & Chr(13) _
& Chr(13) _
& " You have not recorded all Attendance Sessions for the period" & Chr(13) _
& " the pupil has been on roll....." & Chr(13) _
& Chr(13) _
& "Please ensure an Attendance Code is logged for each day the pupil" & Chr(13) _
& "has been On Roll" & Chr(13) _
& Chr(13) _
& "Thank You !!!!!", vbCritical, "Attendance Sessions Conflict"

'// Not sure of course, but would you want to clear/reset the date cell //
'// (U13) here as well (to insist on match)?
End If
End If
End Sub
[/vba]

Hope that helps,

Mark

[bananatang]

Hi Mark,

Thanks once again for your effort and help.

Your code did the trick.

i did not want the date to reset if cell values did not match.

Thanks so much.

Now i can go to bed and sleep easy.

Thanks..

BT