Excel Worksheet BeforeClose won't Work in Word Doc 
Author Message
 Excel Worksheet BeforeClose won't Work in Word Doc

WIN2K & Office 2K:
Using Insert Object From File, I've inserted an Excel Worksheet into a Word
Doc.  The Worksheet contains two tables for the user to input financial
estimates in which the grand totals of each table should be equal. I've
place the following macro in the Workbooks module to assure that the user's
two totals match by not allowing the workbook to close. (The cell G21 holds
the value of the Obligation total minus the Disbur{*filter*}t total):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Range("G21").Value <> 0 Then
      msg = "Obligation & Disbur{*filter*}t Totals MUST Match" & Chr(13) &
Chr(10)
      msg = msg + "You Can Not Close the Work Sheet"
      a = MsgBox(msg)
      Cancel = True
    End If
End Sub

My problem is this: If the user right clicks inside the table in the Word
doc and selects Worksheet - Open, the above macro works fine when the user
tries to exit from Excel w/ unmatched totals.  However, if the user
double-clicks the table or choses Worksheet-Edit off the right-click menu,
the macro doesn't click in and the workbook still closes with unbalanced
data.

In most cases, the user will double-click to input their data.

Can someone please help me with a work-around on this.
--

Chuck Miller



Wed, 02 Jun 2004 23:00:40 GMT  
 Excel Worksheet BeforeClose won't Work in Word Doc
Try handling the Workbook_Deactivate event.

Gerard

Microsoft Office Development with Visual Studio
http://www.*-*-*.com/

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.*-*-*.com/

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.
--------------------

| Newsgroups: microsoft.public.excel.programming
| Subject: Excel Worksheet BeforeClose won't Work in Word Doc
| Lines: 34
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000

| Date: Sat, 15 Dec 2001 15:00:40 GMT
| NNTP-Posting-Host: 4.46.3.73

| X-Trace: newsread1.prod.itd.earthlink.net 1008428440 4.46.3.73 (Sat, 15
Dec 2001 07:00:40 PST)
| NNTP-Posting-Date: Sat, 15 Dec 2001 07:00:40 PST
| Organization: EarthLink Inc. -- http://www.*-*-*.com/
| X-Received-Date: Sat, 15 Dec 2001 07:00:41 PST
(newsmaster1.prod.itd.earthlink.net)
| Path:
cpmsftngxa08!tkmsftngxs01!tkmsftngp01!newsfeed00.sul.t-online.de!t-online.de
!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!netnews.com!xfer02.netnews.
com!newsfeed2.earthlink.net!newsfeed.earthlink.net!newsmaster1.prod.itd.eart
hlink.net!newsread1.prod.itd.earthlink.net.POSTED!not-for-mail
| Xref: cpmsftngxa08 microsoft.public.excel.programming:241786
| X-Tomcat-NG: microsoft.public.excel.programming
|
| WIN2K & Office 2K:
| Using Insert Object From File, I've inserted an Excel Worksheet into a
Word
| Doc.  The Worksheet contains two tables for the user to input financial
| estimates in which the grand totals of each table should be equal. I've
| place the following macro in the Workbooks module to assure that the
user's
| two totals match by not allowing the workbook to close. (The cell G21
holds
| the value of the Obligation total minus the Disbur{*filter*}t total):
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
|     If Range("G21").Value <> 0 Then
|       msg = "Obligation & Disbur{*filter*}t Totals MUST Match" & Chr(13) &
| Chr(10)
|       msg = msg + "You Can Not Close the Work Sheet"
|       a = MsgBox(msg)
|       Cancel = True
|     End If
| End Sub
|
| My problem is this: If the user right clicks inside the table in the Word
| doc and selects Worksheet - Open, the above macro works fine when the user
| tries to exit from Excel w/ unmatched totals.  However, if the user
| double-clicks the table or choses Worksheet-Edit off the right-click menu,
| the macro doesn't click in and the workbook still closes with unbalanced
| data.
|
| In most cases, the user will double-click to input their data.
|
| Can someone please help me with a work-around on this.
| --
|
| Chuck Miller
|
|
|
|



Sat, 05 Jun 2004 02:41:35 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. BeforeClose Problem w/Excel Worksheet in Word Document

2. hyperlinks won't work in a Word forms-protected doc

3. Word .doc files won't save as .doc, only .dot

4. Word 2000 Won't Import Password Protected Word 97 Doc

5. Word doc won't open in Word 2002 w/XP Service Packs

6. Word 2000 .doc won't open in Word 97

7. Word won't work but Excel does

8. Excel X won't print Excel 98 worksheets

9. BeforeClose doesn't work

10. BeforeClose doesn't work right when quitting


 
Powered by phpBB® Forum Software © phpBB Group