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

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

Hi Hari,
I could save and then terminate Excel
So I guess before save would have to
turn off screen updating, and turn off enable events
put the worksheets to the close status
save
then make the sheets available again

But don't know if you can do it that way because
Save is going to happen so I don't think you have
control of what happens after save. I guess you
could just close but that would discourage people
from saving their files periodically for any workbook.

Workbook_BeforeSave



--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"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

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

Hi David,

Thanx for responding.

To be frank your post went completely above my head. Couldnt understand ur
flow of thought. If possible please tell me in novice expression (would like
to learn some new CONCEPTS..)

Also how does workbook_beforesave, enable events, screenupdating fit in my
case.

Regards,
Hari
India

"David McRitchie" wrote in message
...
Hi Hari,
I could save and then terminate Excel
So I guess before save would have to
turn off screen updating, and turn off enable events
put the worksheets to the close status
save
then make the sheets available again

But don't know if you can do it that way because
Save is going to happen so I don't think you have
control of what happens after save. I guess you
could just close but that would discourage people
from saving their files periodically for any workbook.

Workbook_BeforeSave



--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"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

I tried to do a web search but it looks like everything is
from a site that copies newsgroup postings (often unrelated
to each other) and has them on a webpage. Also a lot of
PDF pages that I try to avoid because they take too long to
process. Both generally interfere with web searchs (IMHO),
but perhaps in this case an advantage because in newsgroup
you not likely to have all of those in a single posting, and I don't
know of any provision to search threads. So for this such
sites do provide the means to search the entire thread.
This was my web search:
workbook_beforesave +workbook_beforeclose +workbook_open +visible

I didn't look it over carefully, since it's not the kind of thing I want to do,
but I think it covers the aspect of a user doing their own save.
http://www.experts-exchange.com/Appl..._21065305.html

ScreenUpdating applies because you don't want
people seeing screens flip around as you hide the pages
they are looking at and then unhide them again.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Hari" wrote ...
To be frank your post went completely above my head. Couldnt understand ur
flow of thought. If possible please tell me in novice expression (would like
to learn some new CONCEPTS..)

Also how does workbook_beforesave, enable events, screenupdating fit in my
case.

Regards,
Hari
India

"David McRitchie" wrote in message
...
Hi Hari,
I could save and then terminate Excel
So I guess before save would have to
turn off screen updating, and turn off enable events
put the worksheets to the close status
save
then make the sheets available again

But don't know if you can do it that way because
Save is going to happen so I don't think you have
control of what happens after save. I guess you
could just close but that would discourage people
from saving their files periodically for any workbook.

Workbook_BeforeSave



--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"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

Hi Hari,

What I am suggesting is very close to what David M. has proposed...
In the Workbook_BeforeSave event:
Hide the sheets
Save the workbook using: "ThisWorkbook.Save"
Unhide the sheets
Tell Excel the workbook is saved using: "ThisWorkbook.Saved = True"

Regards,
Jim Cone
San Francisco, CA

"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

Hari,

Opps...
I believe, you will also need to add the line: "Cancel = True" to prevent the event
from saving the workbook again.

Jim Cone

"Jim Cone" wrote in message ...
Hi Hari,
What I am suggesting is very close to what David M. has proposed...
In the Workbook_BeforeSave event:
Hide the sheets
Save the workbook using: "ThisWorkbook.Save"
Unhide the sheets
Tell Excel the workbook is saved using: "ThisWorkbook.Saved = True"
Regards,
Jim Cone
San Francisco, CA

Hi Jim,

Thnx for getting in to the thread.

I actually couldnt understand what David M and urself are suggesting.

I dont understand why we have to hide the sheets and unhide the sheets
within the same session.
I mean shouldnt hiding and unhiding have be done in different sessions. By
different sessions I mean user works on the sheet saves it and closes excel
(stage 1)then tomorrow or some other day open the sheet again (stage 2). Now
in this process wouldnt hiding be done at the completion of stage 1 and
unhiding be done at the starting of stage 2.
Im not able to understand why DM and urself are hiding and unhiding within a
single session. I am not able to understand the logic of doing so. If
possible may be u could give me the full code of what u are saying and may
be this will help me in understanding in where my thinking is going wrong.

Regards,
Hari
India

"Jim Cone" wrote in message
...
Hari,

Opps...
I believe, you will also need to add the line: "Cancel = True" to prevent
the event
from saving the workbook again.

Jim Cone

"Jim Cone" wrote in message
...
Hi Hari,
What I am suggesting is very close to what David M. has proposed...
In the Workbook_BeforeSave event:
Hide the sheets
Save the workbook using: "ThisWorkbook.Save"
Unhide the sheets
Tell Excel the workbook is saved using: "ThisWorkbook.Saved = True"
Regards,
Jim Cone
San Francisco, CA

Hari,

Please just forget I stuck my head in here.
Your code works, mine has problems.

Regards,
Jim Cone
San Francisco, CA

"Hari" wrote in message ...
Hi Jim,
Thnx for getting in to the thread.
I actually couldnt understand what David M and urself are suggesting.
I dont understand why we have to hide the sheets and unhide the sheets
within the same session.
I mean shouldnt hiding and unhiding have be done in different sessions. By
different sessions I mean user works on the sheet saves it and closes excel
(stage 1)then tomorrow or some other day open the sheet again (stage 2). Now
in this process wouldnt hiding be done at the completion of stage 1 and
unhiding be done at the starting of stage 2.
Im not able to understand why DM and urself are hiding and unhiding within a
single session. I am not able to understand the logic of doing so. If
possible may be u could give me the full code of what u are saying and may
be this will help me in understanding in where my thinking is going wrong.

Regards,
Hari
India

- snip -