Message Box and Return to Required Cell

[ruthverkuyl]

I am trying to force a user response to a cell, when the user leaves the cell blank and tries to navigate to another sheet, the message box would remind them of required entry and return to the sheet and cell.

I have set up on deactivate sheet to run message box when cells are false which works, but I can't seem to get it right to return to the sheet. It flashes the tab that it is activating but it is in a loop and I have to force quit.
Thank you for any assistance
Ruth


Private Sub Worksheet_Deactivate()
If Range("E69").Value = False Then
If Range("F69").Value = False Then

MsgBox "Required to answer meets competency requirements YES or NO.", vbOKCancel

Sheets("Scoring").Activate
Range("E69").Select
End If

End If
End Sub

 

[Derek Brown]

Hello and welcome to The Board.
I don't have a solution for you but I can tell you that the code you gave does work for me when I enter it into a new workbook.
Do you have any other VBA code in the workbook that could be responsible for the problem?

 

[mikerickson]

Something like this might be a smoother user interface.
In worksheet Scoring's code module

Private Sub Worksheet_Deactivate()
If Range("E69").Value = False Then
    If Range("F69").Value = False Then
        With Sheets("Scoring").Range("E69")
            Select Case MsgBox("Does this person meet Comptency", vbYesNoCancel)
                Case Is = vbYes
                    .Value = "YES"
                Case Is = vbNo
                    .Value = "NO"
                Case Else
                    .Parent.Activate
            End Select
        End With
    End If
End If
End Sub