Solved: Save one sheet as Read-Only

[debauch]

Hi there.

There is a list we use at work of 2000 or so users, and it is consistantly incorrect. I am working to create an interface to dummy-proof the process. All the excel sheets are hidden in the background and not accessible, unless you have admin rights to my app.

My question is, how can I create a copy of one of the tabs, and save it as read-only or password protected. What I want to do is: once they have made all the updates in the interface, save the one sheet so they cannot make any updates manually once they are ready to post the list. The below I was using to create a copy, but it leaves the opportunity to change data.
[vba]
Sheets("BigList").Copy
[/vba]
Thoughts?

[Bob Phillips]

How about hiding it

[vba]

WorkSheets("BigList").Copy
With Activesheet
.Visible = xlSheetVeryhidden
End With
[/vba]

[debauch]

Hey, thanks for the response.

The sheet actually hidden the begin with. The userform inputs the data onto the sheet. THis way, they cannot simpy drag things down, and input incorrect values.

When they are done making the updates using the userform , I want them to save the hidden sheet to the public drive. THing is, I can get it to copy to a seperate workbook, and save it as a name , but Im not sure how to make the new workbook have a password, or make it read-only. Is it possible?

[Simon Lloyd]

Try this:
[VBA]
WorkSheets("BigList").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Simon\My Documents\Biglist.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
[/VBA]this saves it as read only without passwords to my documents, substitute Simon for your name.

Regards,
SImon

[Simon Lloyd]

If you dont want to specify the path on your PC you could use this, it will save your sheet as a read only workbook to the path where the workbook is stored saving it as the worksheet name and todays date.
[VBA]
Sub SaveAndDate()
WorkSheets("BigList").Copy
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "/" & ActiveSheet.Name & " " & Format(Date, "dd mmm yyyy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
End Sub
[/VBA]Regards,
Simon

[debauch]

That works great! Thank-you.

This way no-one will ever see the list unitl its read-only. sweeet!

[debauch]

I actually found this worked best to p/w & read-only the new workbook.
[VBA]
ActiveSheet.Protect Password:="biglist07946"
[/VBA]
Using that with Simon's save function did the trick.