Getting around Protected Worksheet (number format)

[Al 305]

Hi..
I have this command which put a timestamp on a cell every time you
write something in another cell.. I then use a NumberFormat command to
put the timestamp in H:MM.. But when i protect my worksheet (this
Worsheet Needs to be protected), it gives an error.. I am trying to
get a way around it.. My first idea is to have an other worksheet that
would =sheet1! .. This worsheet (2) wouldnt be protected, so the
number format macro would work.. My problem is that since the worsheet
(2) is not the active, it does not recalculate the timestamp
function.. Here is the Command:

Private Sub Worksheet_Activate()
Call Recalculate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("P13").Address Then
Range("H13").Value = Now
End If

If Target.Address = "$H$13" Then
Target.NumberFormat = "h:mm"
End If
End Sub

I am sure that there are different ways to solve this problem..Anybody
would know a way around this?

 

[Peter T]

Not sure I totally follow, if the sheet is protected how does the "P13" cell
end up being changed. Perhaps in the event with code you could unprotect,
write the new value, then re-protect Or maybe leave your cell(s) unlocked.

or write to the other sheet

If Target.Address = Range("P13").Address Then
worksheets("Sheet2").Range("H13").Value = Now
End If

or use the CodeName if "Sheet2" might get renamed
Sheet2.Range("H13").Value = Now

Regards,
Peter T

 

[Al 305]

Hi Peter..
Sorry about the info.. Both P13 cells and H13 cells were unlocked,
but i was still getting the error.. but with you code, i m able to
divert the result in sheet2 (which i wont protect and try to Hide),
and then, from Sheet1 (H13), refer to the info in sheet 2... At least
it works... Thks.. Unless you have a code that would say something
like:

(in worksheet1)
if there is an input in P13
then Unlock Worsheet 1
Put Now() in H13
NumberFormat H13: H:MM
Lock Worksheet 1

What do you think?

 

[Peter T]

Sorry about the info.. Both P13 cells and H13 cells were unlocked,

but i was still getting the error..

Are you sure, if H13 is unlocked sheet protection shouldn't be a problem.

Here's some basic unprotect/re-protect code, depending on your version you
may want to include additional protect options.


Private Sub Worksheet_Change(ByVal Target As Range)
Const PWRD As String = "abc"

On Error GoTo errExit
If Target.Address = Range("P13").Address Then
Application.EnableEvents = False
Me.Unprotect PWRD

With Range("H13")
.Value = Now
.NumberFormat = "h:mm"
End With

Me.Protect Password:=PWRD, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End If

errExit:
Application.EnableEvents = True

'' uncomment for testing
' If Err.Number Then
' MsgBox Err.Description
' Stop
' Resume
' End If
End Sub

Regards,
Peter T