Worksheet CLOSE related macros

[DKS]

Hi,

When a user does a CLOSE of a worksheet, I want to run certain macros. I
can capture the event of closing the workset. So no sweat there.

But in my macro, I want to take certain actions depending on whether the
worksheet is yet to be saved (Yes or No). Meaning, if there are no changes
to be saved then I want to exit the macro; otherwise I want to run the macro.

Other than using a public boolean field that maintains a status on is the
worksheet changed, is there any other way I can get excel to give me this
information?

Many thanks in anticipation.

(AB123).

 

[Mike H]

Hi,


If ThisWorkbook.Saved Then
'Do Nothing
Else
Call MyMacro
End If

Mike

 

[Jacob Skaria]

From VBE left treeview search for the workbook name and click on + to expand
it. Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

On sheet activate you can get the key fields to a public variable and check
out this during close..to see whether these fields have changed..

If this post helps click Yes

 

[Jacob Skaria]

Oops..misread your query...Please ignore..

From VBE left treeview search for the workbook name and click on + to expand
it. Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

On sheet activate you can get the key fields to a public variable and check
out this during close..to see whether these fields have changed..

If this post helps click Yes

 

[DKS]

Thanks Mike, this is exactly what I was looking for.

Jacob: thanks for your reply, but I preferred to work without public
variables and so I preferred Mike's solution.