Almost Worked - auto add value in locked cell

A

arielax

Almost WORKED !!
Excelent - almost worked like a charm.


========================
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wks as worksheet
set wks = me.worksheets("sheet1")
with wks.range("A1")
if .value ="" then
.value = date
.numberformat = "MM/DD/YYYY"
end if
end with

End Sub
========================

Issue:
The sheet is password protected. The cell in question is locked s
users can't change the date it was crrated.

Problem:
When I try to save it I get the DEBUG because it is a locked cell an
the sheet is trying to change the value.

Question:
The goal is to have a protected sheet that takes the date first create
and auto enters it. Like a time stamp. The workflow is:

User has a copy (or template) of the Excel sheet that is a reques
form. I want to capture when the form is submitted by the user but i
someone were to ever open this up again i dont want the date to change
i need to know when it was first submitted.

thank
 
D

Dave Peterson

If you know the password:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks as worksheet
set wks = me.worksheets("sheet1")

with wks.range("A1")
if .value ="" then
wks.unprotect password:="hi"
.value = date
.numberformat = "MM/DD/YYYY"
wks.protect password:="hi"
end if
end with

End Sub
 
A

AlfD

Hi!

In your workbook_beforesave routine you need to disable protection
then save the data: then re-enable protection. See Protect an
Unprotect Methods in VBA help file.

Al
 
Top