Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("P7").Value = "Correct" Then
If InStr(1, Range("O7").Formula, "average", vbTextCompare) = 0 Then
MsgBox "Please use the AVERAGE formula"
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case True
Case Range("O7").Value = ""
' The user hasn't given an answer so just clear any message that might be in P7
Range("P7").Value = ""
Case WorksheetFunction.Average(Range("N7:N11")) = Range("O7").Value
' Correct answer but see if he's used a formula
If InStr(1, Range("O7").Formula, "average", vbTextCompare) > 0 Then
Range("P7").Value = "Correct"
Else
Range("P7").Value = "Correct put please use the AVERAGE function"
End If
Case WorksheetFunction.Average(Range("N7:N11")) <> Range("O7").Value
' Incorrect answer
Range("P7").Value = "Incorrect"
Case Else
Range("P7").Value = "Incorrect"
End Select
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ErrorRoutine
Select Case True
Case Range("O7").Value = ""
' The user hasn't given an answer so just clear any message that might be in P7
Range("P7").Value = ""
' We know at this point that there's something in O7 so see if it's the right answer
Case WorksheetFunction.Average(Range("N7:N11")) = Range("O7").Value
' Correct answer but see if he's used a formula
If InStr(1, Range("O7").Formula, "average", vbTextCompare) > 0 Then
Range("P7").Value = "Correct"
Else
Range("P7").Value = "Correct but please use the AVERAGE function"
End If
Case WorksheetFunction.Average(Range("N7:N11")) <> Range("O7").Value
' Incorrect answer
Range("P7").Value = "Incorrect"
Case Else
Range("P7").Value = "Incorrect"
End Select
Application.EnableEvents = True
Exit Sub
ErrorRoutine:
Range("P7").Value = Range("O7").Text & " put whatever error message you want here"
Application.EnableEvents = True
End Sub
=IF(O7 = AVERAGE(N7:N11),"Correct",
If you wanted to check if there was actually a formula in the cell you could use VBA to examine O7.Formula and if Instr finds "AVERAGE" then it's OK otherwise Msgbox "Please use a formula"