Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close Event and Excel Saving process
Hi,
1. I have a file in which I have workbook_Open event in which I hide some sheets and unhide some sheets I have my macro security set to medium. When I open this workbook I get a message for enabling or disabling macros. I chose disabling macros and open the file. I dont do any changes and close the file as it is, but Excel asks me whether I want to save my changes or not. Why is this happening. When neither myself nor the workbook_open event has operated why should excel ask me for saving changes or not? (JFYI, I have a workbook_close event also in this workbook but when macros havent been enabled then I believe that shouldnt affect the workbook anyway..) 2. In the same workbook now I do something else. This time I choose Enable macros when I open the file. On doing the same some sheets get hidden and some sheets get visible. Now without doing any changes in the workbook I close the workbook. Excel doesnt prompt me for saving changes. Why? I believe when the workbook_open event ran it changed the file and that should qualify it for it to be saved.? Please educate me to why it is so I have pasted the code in the workbook module below. Private Sub Workbook_Open() Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="trainingformat" Sheets("Interpretation").Visible = True Sheets("Interpretation").Select Sheets("Instructions").Visible = True Sheets("Raw Data").Visible = True Sheets("Overall New Hire summary").Visible = True Sheets("Overall Crosstraining Summary").Visible = True Sheets("PST Class wise Summary").Visible = True Sheets("CCT Class wise Summary").Visible = True Sheets("Training Plan").Visible = True Sheets("Warning").Visible = xlVeryHidden ActiveWorkbook.Protect Password:="trainingformat" Application.ScreenUpdating = True ThisWorkbook.Save End Sub Private Sub Workbook_beforeclose(Cancel As Boolean) Application.ScreenUpdating = False If ThisWorkbook.Saved = True Then ActiveWorkbook.Unprotect Password:="trainingformat" Sheets("Warning").Visible = True Sheets("Warning").Select Sheets("Interpretation").Visible = xlVeryHidden Sheets("Instructions").Visible = xlVeryHidden Sheets("Raw Data").Visible = xlVeryHidden Sheets("Overall Crosstraining Summary").Visible = xlVeryHidden Sheets("Overall New Hire summary").Visible = xlVeryHidden Sheets("PST Class wise Summary").Visible = xlVeryHidden Sheets("CCT Class wise Summary").Visible = xlVeryHidden Sheets("Training Plan").Visible = xlVeryHidden ActiveWorkbook.Protect Password:="trainingformat" ThisWorkbook.Save Cancel = False Else MsgBox "Please save the workbook before exiting" Cancel = True End If Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call consolidate_macro End Sub Regards, Hari India |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close Event and Excel Saving process
The saved property will be reset if you have any volatile functions in
your sheet (e.g., NOW(), TODAY(), RAND(), AREAS(), CELL(), COLUMNS(), INDEX(), INDIRECT(), OFFSET(), ROWS()), prompting the "Do you want to save" dialog. In article , "Hari" wrote: 1. I have a file in which I have workbook_Open event in which I hide some sheets and unhide some sheets I have my macro security set to medium. When I open this workbook I get a message for enabling or disabling macros. I chose disabling macros and open the file. I dont do any changes and close the file as it is, but Excel asks me whether I want to save my changes or not. Why is this happening. When neither myself nor the workbook_open event has operated why should excel ask me for saving changes or not? (JFYI, I have a workbook_close event also in this workbook but when macros havent been enabled then I believe that shouldnt affect the workbook anyway..) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close Event and Excel Saving process
Hi JEM,
Thnax for responding. I do have one volatile function -- Indirect Why is it that saved property reset (as per your reply I understand that reset means excel considers it to be unsaved, is it so?) operate only when macros are not enabled. I mean when macros are enabled I dont get the equivalent of "Do you want to save" dialog. Regards, Hari India "JE McGimpsey" wrote in message ... The saved property will be reset if you have any volatile functions in your sheet (e.g., NOW(), TODAY(), RAND(), AREAS(), CELL(), COLUMNS(), INDEX(), INDIRECT(), OFFSET(), ROWS()), prompting the "Do you want to save" dialog. In article , "Hari" wrote: 1. I have a file in which I have workbook_Open event in which I hide some sheets and unhide some sheets I have my macro security set to medium. When I open this workbook I get a message for enabling or disabling macros. I chose disabling macros and open the file. I dont do any changes and close the file as it is, but Excel asks me whether I want to save my changes or not. Why is this happening. When neither myself nor the workbook_open event has operated why should excel ask me for saving changes or not? (JFYI, I have a workbook_close event also in this workbook but when macros havent been enabled then I believe that shouldnt affect the workbook anyway..) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - on a button event, open another closed file, post changes, close file | Excel Programming | |||
Resetting Excel commandbars on close event | Excel Programming | |||
Trapping Excel Close Event | Excel Programming | |||
Trapping Excel Close event | Excel Programming | |||
Excel main window close event | Excel Programming |