bananatang
08-29-2009, 03:26 PM
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
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
Thank
BT
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
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
Thank
BT