Excel VBA - How to control live work in Workbook after executing a macro

Hi,

I've created a macro that opens a workbook. Using the macro I protect that workbook, keeping some cells available for editing. The User should be able to work in the workbook, and when the work is finished then the work can be saved.

Problem is: when the workbook becomes available to the User, the User can press File > Save As, and save the workbook somewhere, and also I have no control on when the user finishes the work. How can I give a button indicating Save (I know how to give a button), so that that button triggers saving of the open file.
Sanjay GandhiFounder, KenhalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
You can actually capture the before save event.  Just place the following in the workbook VBA.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Whatever you want to do
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjay GandhiFounder, KenhalAuthor Commented:
Hi,

Busy schedule, regret delayed reply.

I tried the following code, but it did not show anything. Where am I going wrong?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   '
   ' Check if the file is being saved
   '
   MsgBox "You are not allowed to save the file with a different name", vbCritical, "File Save As"
End Sub

Open in new window


Also how to use these arguments - SaveAsUI, and Cancel

-San
0
Sanjay GandhiFounder, KenhalAuthor Commented:
...continued from above reply.

It only works on my macro file. As I mentioned, when I open another file, and Save As the other file, the person can save that other file with another name. Is there a way, I can stop the User from saving the additionally opened file.

- San
0
Sanjay GandhiFounder, KenhalAuthor Commented:
Fine, as long as saving current file is concerned, it works fine. I guess there is no answer to keeping a check on another file.

San.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.