Creating Protected Excel Workbooks

Is it possible to [automatically create] an Excel spreadsheet with a certain range editable by the client? We were trying the attached template. However, all cells could be edited instead of certain cells. The cells to be edited are under the customer section. We need a way for the customer to edit that portion of the COA. To date, we have been making the COA revisions but it is too cumbersome. We would rather send out this Excel COA with a section for them to complete as they wish. Please let me know as this would save us several hours per week.

Yes, you can have an Excel template that creates workbooks with only specific cells enabled for editing. Just unlock the cells you want your client to be able to edit. To unlock a cell, right-click, choose Format Cells and on the Protection tab clear the Locked option. Locking cells has no effect until the worksheet is protected but you can’t protect the worksheet in the template since that would prevent the template’s AfterTransferFromLIMS macro from making any changes to the worksheet. So, the solution is to add the following single line of VBA code to the bottom (above the End Sub statement) of the template’s SaveReport macro:

Worksheets(1).Protect

Password:=”abc123″,

DrawingObjects:=True, Contents:=True,

Scenarios:=True

Enter your own case-sensitive password in the line above. Note that anyone who has access to the template can “see” the password in the VBA code. But workbooks created from the template have no macros when you remove the infrastructure sheets so you must make sure the Remove Infrastructure option on the Settings sheet in your template is enabled. The SaveReport macro is only invoked when Remove Infrastructure is enabled.