Making a field compulsary before user leaves sheet

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

On Saturday, May 26, 2012 4:33:06 PM UTC-4, Rebecca Ellis wrote:
> 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
>
>
>
>
> --
> Rebecca Ellis

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

Originally Posted by James Ravenswood

On Saturday, May 26, 2012 4:33:06 PM UTC-4, Rebecca Ellis wrote:
> 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
>
>
>
>
> --
> Rebecca Ellis

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

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

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.