workbook beforeclose problem

[taralmehta15]

Hi friends,
I am using a few files and sharing them on google drive and many times more than 1 person opens the same file and work on them, vich leads to conflict files in google drive. To prevent that, I started using workbook_open code vich enters the username in a cell "O2" and saves the file so if sum1 else opens the file at same time, It gives a warning message. Here is the code:

Please Login or Register  to view this content.

This Works fine.
Before closing the file, I delete that cell so next time ne1 else opens the file (i.e if a single person has the file open), there will be no warnin mesage. This also works fine when the person wants to save the file after the changes. Here is the code:

Please Login or Register  to view this content.

Here is the problem I am facing. If a user opens the file and does some changes for eg. calculates nething or selects other cell, but does not want to save the changes and just closes the file, the work_beforeclose will save the changes after deleting the cell "O2".
If I enable alerts to true, before closing, I vl get message whether i want to save the file or not and if i select "no",workbook will not save the changes (will not save after deletin the cell "O2") and next time even if no1 else has the file open, I will get warning message (bcoz file was not saved last time after deleting the cell "O2" as user decided not to save the changes).

Also, when the file remains idle for sometime like 10min or so, and then I want to close the file, excel crashes (file works fine if i delete before_close from it)

Kindly give some suggestions to solve both the problems.
Thank you.

[mehmetcik]

If you used before close to run a macro in the users personal workbook

Then the personal workbook macro could close the workbook either before or after saving it.

It could then clear your flag in cell o2 in the closed workbook, regard of whether the file was saved or not.

You could load a desperate macro on the users personal workbook to unhide the sheets in your file.
This is simply to ensure that no-one who hasn't loaded the macro in the personal workbook cannot acess the workbook.

[taralmehta15]

Sorry, I dint get what u r sayin..

There is only 1 workbook being shared between all users through google drive. (I mean multiple workbooks are shared but all r different and user has to open the workbook from google drive and after making changes, user saves file to google drive only, no personal workbook for different users)

[mehmetcik]

Ok Try this

Start your macro recorder:

Select Developer, Record Macro.

your recorder window opens.

Where it says record Macro in: Select "In Personal Workbook" giving the macro a short cut key like "u"

Select A1 then Select F1.

Stop the Recorder.


Close excel. Make sure you select Save Changes in Personal workbook.

Now open Excel in a blank spreadsheet.


Select A5.

now press ctrl and u.

Your cursor will flash to A1 and then to F1.

Why? Because your personal workbook is a hidden workbook that is open when excel is open.
Any Macro in that workbook are always available to the user.

[taralmehta15]

oh k.. But, After running my code (to clear cell "O2") from the personal workbook, it will still ask the user to save the file, right?

Here I am attaching a trial sheet, maybe it vl give a better idea..
try the following:
1st time u open the sheet, enter ne data and close the file. Select yes when asked to save the file (Data u added vl be saved along with clearing of cell O2) . Open the file again. It would work fine.
now enter some data, again close the file, When asked to save the file, Select NO (thinking that the user just wrote sumthing but does not want to save the changes). Then again open the file. Here U vl get the warning message (coz cell O2 is not empty).



So I was thinking of ne way by vich the changes entered by user can be discarded and then Cell O2 is cleared before saving the file.