Open/Close Event and Excel Saving process

[Hari]

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

 

[JE McGimpsey]

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.

 

[Hari]

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