Some simple questions concerning Protection, and File/Save

[Angelo Dundee]

ray:
Can someone plaese help
1) If i have a spreadsheet program which consists of a few sheets, do I have to protect each one individually, or is there a simple way of protecting all of them ?
2) What does the Protect Workbook command do, as oppose to the Protect Worksheet command ?
3) How can I stop the master spreadsheet program, from saving onto itself, i.e.
a) I want to prevent the user from using the File/Save command
b) I want to prevent the user from using the File/Save As command/ then choosing the same file name
In order to prevent the master copy from disappearing ?

Thanks !

 

[santeria]

Re: Some simple questions concerning Protection, and File/Sa

What version of Excel are you using ??

 

[Iridium]

Re: Some simple questions concerning Protection, and File/Sa

To answer some of your questions:

http://j-walk.com/ss/excel/usertips/tip022.htm
http://j-walk.com/ss/excel/faqs/protectionFAQ.htm

and a couple of specimen macros to unprotect/reprotect all sheets

Sub ProtectAllSheets()
Dim x
For x = 1 To Sheets.Count
Sheets(x).Protect ("password")
Next x
End Sub

Sub UnProtectAllSheets()
Dim x
For x = 1 To Sheets.Count
Sheets(x).Unprotect ("password")
Next x
End Sub

HTH

 

[Iridium]

Re: Some simple questions concerning Protection, and File/Sa

Just found this in my personal.xls to disable saveas - can't remember who wrote it though!

Goes in the ThisWorkbook module and obviously requires macros to be enabled

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Select Case True
Case SaveAsUI
MsgBox "Sorry, you may not ''Save as'' this workbook." & vbCrLf & vbCrLf & "You may only save it, _ keeping its original name.", 48, "''Save as'' not allowed."
Cancel = True
Case Else
Exit Sub
End Select
End Sub

HTH also

 

[GorD]

Re: Some simple questions concerning Protection, and File/Sa

With regard to the last part of your question, in the "save as" menu select "tools" then "general options" you can add a password to allow modification. Otherwise the user would have to open as read only. I think that should work

 

[Angelo Dundee]

Re: Some simple questions concerning Protection, and File/Sa

Santeria, I am using 2002

Any help you can giv me will be greatly appreciated.

Thanks, A. D.

'nobody is unbeatable, not even Foreman.'

 

[GorD]

Re: Some simple questions concerning Protection, and File/Sa

The difference with the Protect workbook command is that it only prevents the following:

workbook window from resizing and movement
adding/deleting/renaming/moving/hiding/unhiding worksheets,

 

[Angelo Dundee]

Re: Some simple questions concerning Protection, and File/Sa

Iridium, thanks for your help.

That is similar to what I want to do, however, your solution does the opposite of what i want to do, i.e.
I want the user to be prevented from using the Save command ( not the Save As command ! ), also
I want the user to be prevented from using the Save As command / followed by the current filename ( this is obviosly the same as Save )

What is the code to do both of these things ?
ray:

 

[Iridium]

Re: Some simple questions concerning Protection, and File/Sa

I'm no VBA guru by the way but try this:

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

Not sure though... Post back if I've ****ed up

 

[TryingToLearn]

Re: Some simple questions concerning Protection, and File/Sa

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fname
    Select Case SaveAsUI
        Case True
            fname = Application.GetSaveAsFilename
            If InStr(1, UCase(fname), UCase(ThisWorkbook.Name)) Then
            MsgBox "Sorry, you may not save as the existing name."
            End If
            Cancel = True
            Exit Sub
        Case False
            Cancel = True
            MsgBox ("Ya can't save to there from here")
            Exit Sub
    End Select
End Sub

p.s. Iridium is one of the many guru's here I've relied on to learn this stuff in the past few months so don't listen to his modesty.