Excel - save read only and protected VBA

[Pobo]

Hi

Can anyone tell me the best way to automate the saving of a file as read only and to protect the worksheet so that cells cannot be edited or copied.

At the moment I am using the following code:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlNoSelection
    Range("B7").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Administrator\My Documents\Book2.xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
    ActiveWorkbook.Close

But when you reopen the workbook the cells cannot be edited but the dis-'EnableSelection' setting seems to have been reset and users are able to select cells again.!!!!??

Has anyone got any suggestions please?


Many thanks

Pobo

[AIS4U]

Pobo:

I am not really an Excel guy, but have you tried setting the protection through Tools>Protection>Protect Sheet in your Excel document.

I think if you did that then the protection would exist even when you do your Visual Basic manipulation or the Excel wookbook.

I may be wrong.

Good Luck

[Pobo]

Thanks

That does work but I need to do it using vba during execution.

[zaza]

Have you tried:

ActiveWorkbook.Protect

Look it up in VBA Help for more info. You can set the password to help you Unprotect it.

zaza

[Pobo]

Yes, I have but when you close and reopen the workbook you can select the cells. If you do it manually using the Tools menu options when you reopen the workbook you can't select the cells but if you do the same thing with vba you can!

[lexII]

Try this in the workbook open event

Private Sub Workbook_Open()
ProtectIt
End Sub
 
Sub ProtectIt()
For SCount = 1 To Sheets.Count
Worksheets(SCount).Protect Password:="KeepOut", DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets(SCount).EnableSelection = xlNoSelection 'xlUnlockedCells
Next SCount
End Sub

Password is optional,

[Pobo]

Thanks - BUT, the workbook is automatically created as an output file from Access - can't add code dynamically!!! OR can you?

[Pobo]

Any more ideas? This one's still not resolved ...

By the way - is it possible to add code dynamically?

[DKenny]

Yes, I have but when you close and reopen the workbook you can select the cells. If you do it manually using the Tools menu options when you reopen the workbook you can't select the cells but if you do the same thing with vba you can!

Just because you have the worksheet protected, it doesn't mean that the individual cells are protected. Goto the Fomat->Cells menu and select the Protection tab. If the cell is not "Locked" then the worksheet protection will have no effect.