Locking documents in Excel after a template has been created.

[Borneval]

I have created a template to use at my office that I need to share with other
employees to use. The template is locked with only necessary cells opened
for data entry. The issue I'm having is these templates need to be locked
completely once the data has been entered so they cannot be altered. I can't
lock the worksheet completely without first unlocking the template. Is there
was way to share a locked template that gives others the ability to lock the
data they've entered but not have access to altering any other aspects of the
original template?

 

[RagDyeR]

How is the template retrieved (accessed) by the other users?

Is the template on a shared drive (LAN - network)?

Is the template *copy* renamed and saved to a particular file after data
entry?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I have created a template to use at my office that I need to share with
other
employees to use. The template is locked with only necessary cells opened
for data entry. The issue I'm having is these templates need to be locked
completely once the data has been entered so they cannot be altered. I
can't
lock the worksheet completely without first unlocking the template. Is
there
was way to share a locked template that gives others the ability to lock the
data they've entered but not have access to altering any other aspects of
the
original template?

 

[Gord Dibben]

If it is a true Template(*.XLT), it will not open to be altered.

Only a workbook based on that Template will open.

I would use event code to lock the cells as data is entered in the worksheet.

That event code would be placed into the worksheet in the *.XLT and would lock
the cells in the opened workbook.

To add code to the Template(*.XLT) browse to that file using Windows Explorer.

Right-click to open, add the code then Save

Now File>New>Template.

Start adding data to the unlocked cells which will become locked when ENTER key
is hit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String = "A1,B2,C3,D4,E5,F6"
On Error GoTo enditall
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
If Not Intersect(Target, Me.Range(sINPUTS)) Is Nothing Then
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP