Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet CLOSE related macros
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet CLOSE related macros
Hi,
If ThisWorkbook.Saved Then 'Do Nothing Else Call MyMacro End If Mike "DKS" wrote: 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet CLOSE related macros
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 "DKS" wrote: 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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet CLOSE related macros
Oops..misread your query...Please ignore..
"Jacob Skaria" wrote: 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 "DKS" wrote: 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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet CLOSE related macros
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. "Mike H" wrote: Hi, If ThisWorkbook.Saved Then 'Do Nothing Else Call MyMacro End If Mike "DKS" wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to run macros as one close a excel? | Excel Programming | |||
close WB after clicking DISABLE MACROS | Excel Programming | |||
Extend Rows and related formula downwards using functions not macros | New Users to Excel | |||
Search a worksheet and add related cells | Excel Worksheet Functions | |||
Excel crashes on close - macro related | Excel Programming |