If values are'nt entered in cell a popup window comes up??
 

Hi again,
I have sheets called back1 thru to back35. In all the sheets Cell I2
and Cell J2 must have a start time and end time entered before a user
exits the sheet ie: 0700 1526.

Now this is easily forgotten and I would like to popup a window or
form if they try to exit the sheet without putting those times in,
which reminds them to enter the times.

Is it possible???

Help appreciated.
Stephen

If values are'nt entered in cell a popup window comes up??
 

Try this, which is placed in "This Workbook" code:

Sub workbook_beforeclose(cancel As Boolean)

Dim wsn As Worksheet

For ws = 1 To Worksheets.Count

Set wsn = Worksheets(ws)
If Left(wsn.Name, 4) = "back" Then
If Application.Or(wsn.Range("i2") = 0, wsn.Range("j2") = 0) Then
MsgBox "Please ensure times are entered in .." & wsn.Name
cancel = True
End If
End If
Next ws

End Sub

HTH

"pano" wrote:

Hi again,
I have sheets called back1 thru to back35. In all the sheets Cell I2
and Cell J2 must have a start time and end time entered before a user
exits the sheet ie: 0700 1526.

Now this is easily forgotten and I would like to popup a window or
form if they try to exit the sheet without putting those times in,
which reminds them to enter the times.

Is it possible???

Help appreciated.
Stephen

If values are'nt entered in cell a popup window comes up??
 

On Feb 14, 10:33 pm, Toppers
wrote:
Try this, which is placed in "This Workbook" code:

Sub workbook_beforeclose(cancel As Boolean)

Dim wsn As Worksheet

For ws = 1 To Worksheets.Count

Set wsn = Worksheets(ws)
If Left(wsn.Name, 4) = "back" Then
If Application.Or(wsn.Range("i2") = 0, wsn.Range("j2") = 0) Then
MsgBox "Please ensure times are entered in .." & wsn.Name
cancel = True
End If
End If
Next ws

End Sub

HTH



"pano" wrote:
Hi again,
I have sheets called back1 thru to back35. In all the sheets Cell I2
and Cell J2 must have a start time and end time entered before a user
exits the sheet ie: 0700 1526.

Now this is easily forgotten and I would like to popup a window or
form if they try to exit the sheet without putting those times in,
which reminds them to enter the times.

Is it possible???

Help appreciated.
Stephen- Hide quoted text -

- Show quoted text -

Hi sorry posted direct..... I have put this in THIS WORKBOOK but I can
still exit the worksheet without having times in

Regards Stephen

If values are'nt entered in cell a popup window comes up??
 

On Feb 15, 12:07 am, "pano" wrote:
On Feb 14, 10:33 pm, Toppers
wrote:





Try this, which is placed in "This Workbook" code:

Sub workbook_beforeclose(cancel As Boolean)

Dim wsn As Worksheet

For ws = 1 To Worksheets.Count

Set wsn = Worksheets(ws)
If Left(wsn.Name, 4) = "back" Then
If Application.Or(wsn.Range("i2") = 0, wsn.Range("j2") = 0) Then
MsgBox "Please ensure times are entered in .." & wsn.Name
cancel = True
End If
End If
Next ws

End Sub

HTH

"pano" wrote:
Hi again,
I have sheets called back1 thru to back35. In all the sheets Cell I2
and Cell J2 must have a start time and end time entered before a user
exits the sheet ie: 0700 1526.

Now this is easily forgotten and I would like to popup a window or
form if they try to exit the sheet without putting those times in,
which reminds them to enter the times.

Is it possible???

Help appreciated.
Stephen- Hide quoted text -

- Show quoted text -

Hi sorry posted direct..... I have put this in THIS WORKBOOK but I can
still exit the worksheet without having times in

Regards Stephen- Hide quoted text -

- Show quoted text -

Now I see it is set up for the X close on the worksheet, how would I
set it up for a macro, how do I call this routine????????

Stephen

If values are'nt entered in cell a popup window comes up??
 

On Feb 15, 12:15 am, "pano" wrote:
On Feb 15, 12:07 am, "pano" wrote:





On Feb 14, 10:33 pm, Toppers
wrote:

Try this, which is placed in "This Workbook" code:

Sub workbook_beforeclose(cancel As Boolean)

Dim wsn As Worksheet

For ws = 1 To Worksheets.Count

Set wsn = Worksheets(ws)
If Left(wsn.Name, 4) = "back" Then
If Application.Or(wsn.Range("i2") = 0, wsn.Range("j2") = 0) Then
MsgBox "Please ensure times are entered in .." & wsn.Name
cancel = True
End If
End If
Next ws

End Sub

HTH

"pano" wrote:
Hi again,
I have sheets called back1 thru to back35. In all the sheets Cell I2
and Cell J2 must have a start time and end time entered before a user
exits the sheet ie: 0700 1526.

Now this is easily forgotten and I would like to popup a window or
form if they try to exit the sheet without putting those times in,
which reminds them to enter the times.

Is it possible???

Help appreciated.
Stephen- Hide quoted text -

- Show quoted text -

Hi sorry posted direct..... I have put this in THIS WORKBOOK but I can
still exit the worksheet without having times in

Regards Stephen- Hide quoted text -

- Show quoted text -

Now I see it is set up for the X close on the worksheet, how would I
set it up for a macro, how do I call this routine????????

Stephen- Hide quoted text -

- Show quoted text -

I meant to say I have a button to exit on the sheet users wont be
using the close X sheet...

If values are'nt entered in cell a popup window comes up??
 

Have a macro attached to your button(s):

Sub CheckTimesEntered()
Thisworkbook.close
End

This should invoke the "BeforeClose" macro.

HTH

"pano" wrote:

On Feb 15, 12:15 am, "pano" wrote:
On Feb 15, 12:07 am, "pano" wrote:





On Feb 14, 10:33 pm, Toppers
wrote:

Try this, which is placed in "This Workbook" code:

Sub workbook_beforeclose(cancel As Boolean)

Dim wsn As Worksheet

For ws = 1 To Worksheets.Count

Set wsn = Worksheets(ws)
If Left(wsn.Name, 4) = "back" Then
If Application.Or(wsn.Range("i2") = 0, wsn.Range("j2") = 0) Then
MsgBox "Please ensure times are entered in .." & wsn.Name
cancel = True
End If
End If
Next ws

End Sub

HTH

"pano" wrote:
Hi again,
I have sheets called back1 thru to back35. In all the sheets Cell I2
and Cell J2 must have a start time and end time entered before a user
exits the sheet ie: 0700 1526.

Now this is easily forgotten and I would like to popup a window or
form if they try to exit the sheet without putting those times in,
which reminds them to enter the times.

Is it possible???

Help appreciated.
Stephen- Hide quoted text -

- Show quoted text -

Hi sorry posted direct..... I have put this in THIS WORKBOOK but I can
still exit the worksheet without having times in

Regards Stephen- Hide quoted text -

- Show quoted text -

Now I see it is set up for the X close on the worksheet, how would I
set it up for a macro, how do I call this routine????????

Stephen- Hide quoted text -

- Show quoted text -

I meant to say I have a button to exit on the sheet users wont be
using the close X sheet...