Worksheet CLOSE related macros

  • Thread starter Thread starter DKS
  • Start date Start date
D

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).
 
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
 
Oops..misread your query...Please ignore..

Jacob Skaria said:
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
Click to expand...
 
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.
 
Back
Top