Macro to prevent 'save-as'

nicademas

New Member
Joined
Feb 20, 2002
Messages
3
Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??

Thanks,
nica
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
On 2002-02-21 06:58, nicademas wrote:
Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??

Thanks,
nica

By EVERYWHERE, do you mean that the users just save the workbook in whatever directory/folder they happen upon? Or do you mean that you don't want the user to sve the workbook at all?

To stop the user from saving the workbook at all you can do this:

1. Right click on the XL sheet icon to the left of the "File" menu, choose "View Code".
2. Copy and Paste this code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

That will stop them from saving (assuming that they don't turn off macros at startup).

Forcing the user save in a specific directory is a bit more tricky. If this is what you want to do then let me know what the full path of the directory is and I can code this if you want. e.g. "C:TempMyImportantFiles"


_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-02-21 07:32
 
Upvote 0
I need them to be only able to save - in the directory they open it from & not save-as becasue they tend to put the files in the strangest places & very valuable data is lost.

Thanks so much!
Nica
 
Upvote 0
please explain how to perform your first point - do not want them to be able to "save as" only "save." thanks
 
Upvote 0
Nica,
The easiest way would be to customezie each of the users toolbars and remove the saveas option and button. This would prevent "illiterate" users from saving everywhere.

The first save of a file would be prompted for a folder, then they would only be able to overwrite the current file name.

HTH
Cal

PS-I would recommend training the users vs. limiting thier access to functionality, so I hope you have a good reason.
 
Upvote 0
our agency is too large to do that, they create copies of the original file...isn't there a way to use macros to do something
 
Upvote 0
Nica,
If you are talking about a single file, and the user security is set to medium, this would be possible, but to limit all users save options for all files, it would have to be on a single PC basis.

HTH
Cal
 
Upvote 0
I just got a great question via PM.

"How do you add the code that I suggested to a workbook and then save it?"

The easiest answer is to do the following:

After you have made the code changes you need to make.

1. Go to View|Toolbars and choose "Visual Basic"
2. Toggle the 6th control from the left "Design Mode"
3. Save your workbook
4. Close and reopen the workbook. Design mode should be off.
 
Upvote 0
These codes works fine thanks.

But how do you prevent users from taking the file (without opening it) and copy paste it to somewhere else?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top