Create write protected worksheets

[Squidula]

Hi everyone,

Im working on a workbook that imports information from various sources and want it to be able to archive the information when an ActiveX button is clicked.

Ive got it to do all that, but would like the new workbooks that it creates to be write protected. The thinking behind it is that once the info has been archived, that you cant amend it. The only way that you could would be to archive again with the same file name and over write the original, which would be an acceptable method.

So here is the code Ive got for the button control:

Private Sub Archive_Click()Dim Response
Response = MsgBox("Are you sure you want to archive: " & ThisWorkbook.Worksheets("Hours Summary").Range("R2"), vbYesNo)
If Response = vbNo Then End


Dim wb As Workbook
Dim wbName As String
Set wb = Workbooks.Add
ThisWorkbook.Worksheets("Hours Summary").Cells.Copy
wb.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
wb.Worksheets(1).Range("A1").PasteSpecial xlPasteFormats
wbName = ThisWorkbook.Worksheets("Hours Summary").Range("C120") & "\" & ThisWorkbook.Worksheets("Hours Summary").Range("R2")
wb.SaveAs wbName
wb.Close


End Sub

What would I need to add to it so it protects the new workbook from being edited?

The other issue Ive encountered is if you choose not to overwrite an existing file with the same name, it leaves a new workbook with no name open. This is pretty easy to sort out, just by closing it, but this archive workbook needs to be totally fool proof due to one of the users being very bad with computers.

Still very new to vba so learning as I go.

Thank you

Stuart.

 

[diddi]

hi and welcome to the board
you can writeprotect (set read only) using the following

Dim objFile as object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(wb)
'    read only is the first bit of the attributes mask
objFile.Attributes =objFile.Attributes Or 1

you will need the windows scripting reference enabled for this to work.