Hi Tom,

Thnx for the reply.

I thought that if the some wrong data is filled and if the user by mistake
click on the close then wrong data will also get saved. Thats why I wanted
to go ahead with the saving event only if the workbook is already closed.
(Am I wrong in my logic)

In context to the same I want to know this. Suppose the user fills some
wrong data and want to scrap the whole session of data filling and want to
start anew. To do that suppose they click on the close event. In this case
they cant close the workbook unless they save it and I dont want them to
overwrite in the existing version of sheet by saving (they can save a dummy
copy but it is needless effort). So is it possible to get the excel save
message in close event itself. Like excel would prompt for saving "Do U want
to save the changes you made to book3" and accordingly the action would be
taken based on whether Ok, No or cancel is chosen. Basically I want the menu
for save to appear in the close workbook event if the workbook is not saved
already so that user can use his/her discretion.

Also I see one problem now. The code will not run if the user changes the
name of the worksheet. I dont want to protect the workbook as I would like
to give them the ability to add worksheets if wanted. I searched web and
came across a post "Any event to trigger Worksheet Rename ?" in which Bob P
and BrainB have offered solutions. I dont want to use the worksheet_change
and worksheet_activate as they are a little volatile (came across somewhere
about they undoing redo stack and god knows what). The second method
codename property seemed to be good but I dont understand head or tail of
what codename property (My VB/programming level is up to FOR statement and
If then else statement). Please guide me on how to use this code name
property for my case. Like syntax and where to paste this code.

Regards,
Hari
India

"Tom Ogilvy" wrote in message
...
If you want the workbook saved, why prompt the user to save it. Just save
it yourself and go on - you are going to have to save it anyway to save
your
page settings.

I believe in at least one version of excel there was a bug with
ThisWorkbook.Saved when used in the beforeclose event. If a user had that
version, they would could end up in an endless loop.

--
Regards,
Tom Ogilvy

"Hari" wrote in message
...
Hi,

I came across a post in the group where one could hide sheets if macros
arent enabled and unhide them only if they are enabled, so that user is
forced to enable them.

I have a spreadsheet where Im getting data filed from different people.
It
has 3 worksheets out of which worksheet "Raw Data" is for entering data,
"Instructions" is basically a guideline/help file sort of thing
containing
text on how the data in Raw data worksheet is to be entered. Im using
lots
of macros in Raw Data worksheet to check for internal data
inconsistencies
so that in case of problems the person entering data could correct it
rather
than me sending back and asking for corrected version.

The third worksheet "Warning" is basically tells the user to enable
macros.

I wrote the following code and inserted it in workbook module.

Option Explicit
Private Sub Workbook_Open()

Sheets("Instructions").Visible = True
Sheets("Raw Data").Visible = True
Sheets("Warning").Visible = xlVeryHidden
ActiveWorkbook.Save

End Sub

Private Sub Workbook_beforeclose(Cancel As Boolean)
If ActiveWorkbook.Saved = True Then

Sheets("Warning").Visible = True
Sheets("Instructions").Visible = xlVeryHidden
Sheets("Raw Data").Visible = xlVeryHidden
ActiveWorkbook.Save
Cancel = False

Else

MsgBox "Please save the workbook before exiting"
Cancel = True

End If

End Sub

Please note it seems to work as I wanted but I wanted to know is whether
am
missing something in the above code (I mean logic wise).

Also is there a more efficient way of writing (or getting the macro
executed) in the above macro.


Regards,
Hari
India