Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use:

H

harpscardiff

I’ve searched as much as I can, to find out which how to disable sav
completely, so effectivly, all ways to save should be disabled. Th
user won’t need to save the document.

This is what I have found:

1.

Code
-------------------


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Msgbox "The 'Save As' function has been disabled.", vbInformation, "Save As Disabled"
Cancel = True
End If
End Sub

-------------------


2.


Code
-------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close SaveChanges:=False
End Sub

-------------------


3.

Code
-------------------

Private Sub StopSave()
' Trap and call the OurSaveProcedure() macro when Control-S is pressed
If Application.OnKey("^s", "") Then ' Prefix ^ (caret) for Ctrl key
Msgbox "Save has been disabled"
End If
End Sub

-------------------


4.

Code
-------------------

Public Sub MenuSave(Enable As Boolean)

'////////////////////////////////////////////////////////'
'/ /'
'/ - Worksheet Menu and Standard Menu - /'
'/ Enable or Disable Save Menu Option /'
'/ /'
'////////////////////////////////////////////////////////'

'Written April 25, 2005
'Author: Leith Ross
'E-mail: (e-mail address removed)

Dim Status
Dim CmdBar1 As CommandBar
Dim CmdBar2 As CommandBar

Set CmdBar1 = Excel.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar

Status = Enable

For I = 1 To CmdBar1.Controls.Count
CtrlName = CmdBar1.Controls(I).Caption
If CtrlName = "&Save" Then
CmdBar1.Controls(I).Enabled = Status
End If
Next I

Set CmdBar2 = Excel.CommandBars("Standard")
CmdBar2.Controls("Save").Enabled = Status

End Sub

-------------------


Which is the best to use?

Apprecaite your time
 
G

Guest

Hello!

Do you want to suppress 'save as' only, or saving in general?

To disable all kind of save-functionality this should work:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Msgbox "Saving has been disabled.", vbInformation
Cancel = True
End Sub

Regards,
Herbert
 
J

Jezebel

Don't bother. If the user really wants to save the workbook, there's
utlimately nothing you can do to stop them. All of your methods would be
defeated simply by opening the workbook with macros disabled. Or by
switching to VBA and saving the workbook manually. Or by using Customise to
reassign the Save command to any toolbar or menu. Etc.

In practice, it's just as effective to put a note in the workbook: "Please
don't save this workbook."




"harpscardiff" <[email protected]>
wrote in message
 
H

harpscardiff

Thanks for your response.

I want to disable all kinds of save functionality: The code which you
stated only works with file> save as.

Any ideas which code disables all save functionality?

Thanks
 
H

harpscardiff

The way my document works is on open a form pops up, once the form is
completed it tranposes the data onto the worksheet. So effectivley to
complete the form they must have Macros enabled.

Also if I disabled most of the saved options: Shortcut, File save as
and icon, the users will quit trying. As there knowledge of Excel of
quite slim. With the expections of a few users.

Thanks
 
G

Guest

The code in my message works whether you click on the save icon in the
toolbar or you use File/Save or File Save As or the save icon in the toolbar
of the VBA editor. At least it does here.

But as Jezebel stated, you have to make sure users aren't able to disable
VBA execution. I don't know if there is an easy way to do that. You could use
policies and certificates, but that is really far fetched :)
 
H

harpscardiff

Herbert - Thanks alot, the code does work.

Now that i've entered the code, how do save the change ready for the
users?
 
C

Chip Pearson

Open the Immediate Window in VBA (Ctrl+G), and enter the
following lines, each followed by ENTER:

Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True


Of course, your users could do exactly the same thing to save the
file.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"harpscardiff"
in message
news:[email protected]...
 
J

Jezebel

They can also open the document with macros disabled and defeat the exercise
entirely.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top