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

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.