Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-08-13
5
Medium Priority
?
464 Views
Last Modified: 2014-08-25
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.
0
Comment
Question by:sanjay-gandhi
  • 3
4 Comments
 
LVL 21

Accepted Solution

by:
Randy Poole earned 1600 total points
ID: 40257950
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
 

Author Comment

by:sanjay-gandhi
ID: 40271559
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
 

Author Comment

by:sanjay-gandhi
ID: 40271572
...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
 

Author Closing Comment

by:sanjay-gandhi
ID: 40285016
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question