How do I make sure a certain worksheet is completed properly before
closing Excel?

I have this code that I obtained from various postings in this
newsgroup on each worksheet:
-------

Private Sub Worksheet_Deactivate()
Const rngWeek As String = "nrmWWeek"
Const rngTotal As String = "gTotal"
Const rngLast As String = "lastDay"

prevSheet = Me.Name

On Error GoTo stoppit
Application.EnableEvents = False

If Me.Range(rngLast).Value <> 0 Then
If Me.Range(rngTotal).Value <> Me.Range(rngWeek).Value Then
Response = MsgBox("Please return to the T&T worksheet
and make corrections", _
vbOKOnly, "Total Hours Error")
Worksheets(prevSheet).Select
End If
End If

stoppit:
Application.EnableEvents = True

End Sub

--------

It checks if whether the value in nrmWWeek match the value in gTotal
and if it doesn't the user is prompted to make a corrections and
activates the previous active worksheet (prevSheet is Global). It
only works when the worksheet is deactivated. However, I also want to
prevent them from closing the workbook before making the corrections.
I know I need to insert 'some' code in the "ThisWorkbook" using
Workbook_BeforeClose but I have no idea how.

I attempted to modify the above and inserted it in the BeforeClose
event but failed. The code


Thank you,

You are right about inserting the before close code in ThisWorkbook. The sub
must be named as follows:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Your code goes here.

End Sub

If the code you are using is the same code for worksheet deactivate and also
workbook close, you can put the code into a module and call the same
procedure from the worksheet deactivate and workbook close. This way, if you
need to modify it it is automatically modified for both. eg the code could
reside in a module under in a procedure named:-

Sub Validate_Entries()

You would then put the following line of code in both the worksheet
deactivate and workbook deactivate procedures:-

Call Validate_Entries

Regards,

OssieMac

"(E-Mail Removed)" wrote:

> How do I make sure a certain worksheet is completed properly before
> closing Excel?
>
> I have this code that I obtained from various postings in this
> newsgroup on each worksheet:
> -------
>
> Private Sub Worksheet_Deactivate()
> Const rngWeek As String = "nrmWWeek"
> Const rngTotal As String = "gTotal"
> Const rngLast As String = "lastDay"
>
> prevSheet = Me.Name
>
> On Error GoTo stoppit
> Application.EnableEvents = False
>
> If Me.Range(rngLast).Value <> 0 Then
> If Me.Range(rngTotal).Value <> Me.Range(rngWeek).Value Then
> Response = MsgBox("Please return to the T&T worksheet
> and make corrections", _
> vbOKOnly, "Total Hours Error")
> Worksheets(prevSheet).Select
> End If
> End If
>
> stoppit:
> Application.EnableEvents = True
>
> End Sub
>
> --------
>
> It checks if whether the value in nrmWWeek match the value in gTotal
> and if it doesn't the user is prompted to make a corrections and
> activates the previous active worksheet (prevSheet is Global). It
> only works when the worksheet is deactivated. However, I also want to
> prevent them from closing the workbook before making the corrections.
> I know I need to insert 'some' code in the "ThisWorkbook" using
> Workbook_BeforeClose but I have no idea how.
>
> I attempted to modify the above and inserted it in the BeforeClose
> event but failed. The code
>
>
> Thank you,
>
>

I've never been a fan of putting stuff in the workbook_beforeclose event.

If I open the workbook in error (or just don't want to save my changes), are you
going to make me enter stuff into the workbook just so I can close it?

And if I have to enter stuff in the workbook, are you going to make me save the
workbook before I can close it? What happens if I open the workbook and destroy
18 worksheets that are very important. If I try to close it, are you going to
force me to save it this way?

Personally, I'd try to do something else.

I like to add a warning message in a cell based on a formula:
=if(somethingisok,"","Warning--xxx and yyy have to be done first!")
(formatted in big bold red letters)

Then I as a user can see the warning and still be able to close without
saving--or close and save and fix at a later time.



"(E-Mail Removed)" wrote:
>
> How do I make sure a certain worksheet is completed properly before
> closing Excel?
>
> I have this code that I obtained from various postings in this
> newsgroup on each worksheet:
> -------
>
> Private Sub Worksheet_Deactivate()
> Const rngWeek As String = "nrmWWeek"
> Const rngTotal As String = "gTotal"
> Const rngLast As String = "lastDay"
>
> prevSheet = Me.Name
>
> On Error GoTo stoppit
> Application.EnableEvents = False
>
> If Me.Range(rngLast).Value <> 0 Then
> If Me.Range(rngTotal).Value <> Me.Range(rngWeek).Value Then
> Response = MsgBox("Please return to the T&T worksheet
> and make corrections", _
> vbOKOnly, "Total Hours Error")
> Worksheets(prevSheet).Select
> End If
> End If
>
> stoppit:
> Application.EnableEvents = True
>
> End Sub
>
> --------
>
> It checks if whether the value in nrmWWeek match the value in gTotal
> and if it doesn't the user is prompted to make a corrections and
> activates the previous active worksheet (prevSheet is Global). It
> only works when the worksheet is deactivated. However, I also want to
> prevent them from closing the workbook before making the corrections.
> I know I need to insert 'some' code in the "ThisWorkbook" using
> Workbook_BeforeClose but I have no idea how.
>
> I attempted to modify the above and inserted it in the BeforeClose
> event but failed. The code
>
> Thank you,

--

Dave Peterson

If you set Cancel=True in BeforeClose than user will not be able to close the
workbook. You can enter the following code:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If yourcond=true then
Cancel = False 'User will be able to close file
else
Cancel = True 'User will not be able to close file
Msgbox "Tell user what to do"
End If
End Sub

"(E-Mail Removed)" wrote:

> How do I make sure a certain worksheet is completed properly before
> closing Excel?
>
> I have this code that I obtained from various postings in this
> newsgroup on each worksheet:
> -------
>
> Private Sub Worksheet_Deactivate()
> Const rngWeek As String = "nrmWWeek"
> Const rngTotal As String = "gTotal"
> Const rngLast As String = "lastDay"
>
> prevSheet = Me.Name
>
> On Error GoTo stoppit
> Application.EnableEvents = False
>
> If Me.Range(rngLast).Value <> 0 Then
> If Me.Range(rngTotal).Value <> Me.Range(rngWeek).Value Then
> Response = MsgBox("Please return to the T&T worksheet
> and make corrections", _
> vbOKOnly, "Total Hours Error")
> Worksheets(prevSheet).Select
> End If
> End If
>
> stoppit:
> Application.EnableEvents = True
>
> End Sub
>
> --------
>
> It checks if whether the value in nrmWWeek match the value in gTotal
> and if it doesn't the user is prompted to make a corrections and
> activates the previous active worksheet (prevSheet is Global). It
> only works when the worksheet is deactivated. However, I also want to
> prevent them from closing the workbook before making the corrections.
> I know I need to insert 'some' code in the "ThisWorkbook" using
> Workbook_BeforeClose but I have no idea how.
>
> I attempted to modify the above and inserted it in the BeforeClose
> event but failed. The code
>
>
> Thank you,
>
>