Making a field compulsary before user leaves sheet

[Rebecca Ellis]

Hi, I am trying to make a field in a sheet mandatory so that when th
user clicks a button I have added to take them to another sheet they ar
prompted that they must fill in a particular cell.

I have used the following code before to make a field compulsary befor
the user closes the whole file but I don't know how to adapt it to wor
when they leave the sheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim checkRng As Range
Set checkRng = Sheets("Sheet1").Range("A1")
If checkRng.Value = "" Then
Cancel = True
MsgBox "Please fill in " & _
checkRng.Address(False, False) & "."
End If
End Sub


Any help would be very much appreciated, thank

 

[James Ravenswood]

Hi, I am trying to make a field in a sheet mandatory so that when the
user clicks a button I have added to take them to another sheet they are
prompted that they must fill in a particular cell.

I have used the following code before to make a field compulsary before
the user closes the whole file but I don't know how to adapt it to work
when they leave the sheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim checkRng As Range
Set checkRng = Sheets("Sheet1").Range("A1")
If checkRng.Value = "" Then
Cancel = True
MsgBox "Please fill in " & _
checkRng.Address(False, False) & "."
End If
End Sub


Any help would be very much appreciated, thanks

Put the following in the worksheet code area (NOT workbook code area) of the worksheet in question:

Private Sub Worksheet_Deactivate()
Dim checkRng As Range
Set checkRng = Sheets("Sheet1").Range("A1")
If checkRng.Value = "" Then
Sheets("Sheet1").Activate
MsgBox "Please fill in " & _
checkRng.Address(False, False) & "."
End If
End Sub

 

[James Ravenswood]

Put the following in the worksheet code area:

Private Sub Worksheet_Deactivate()
Dim checkRng As Range
Set checkRng = Sheets("Sheet1").Range("A1")
If checkRng.Value = "" Then
Sheets("Sheet1").Activate
MsgBox "Please fill in " & _
checkRng.Address(False, False) & "."
End If
End Sub

Hopefully this has not been truncated.

 

[Rebecca Ellis]

Put the following in the worksheet code area (NOT workbook code area) o
the worksheet in question:

Private Sub Worksheet_Deactivate()
Dim checkRng As Range
Set checkRng = Sheets("Sheet1").Range("A1")
If checkRng.Value = "" Then
Sheets("Sheet1").Activate
MsgBox "Please fill in " & _
checkRng.Address(False, False) & "."
End If
End Sub

It works!!!!!!!!!!
Thank you so much for taking the time to reply to my post, I have
deadline this evening for this work and can now send it working!!!
Thank you so much again :-