Protecting/locking spreadsheet stops macro working

[Sam577]

Hi,

I have inserted a date stamp into my spreadsheet with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("G:G").Column Then
Cells(.Row, "N").Value = Now()
End If
End With
Next Cell
End Sub

I want to unlock the column where the date stamp appears to prevent users from amending the time. However, after locking the relevant cells and choosing Tools>Protection>Protect Sheet in Excel, and protecting the sheet with a password, the macro stops working. Is there a way to lock / protect the date stamp values without ruining the macro? I’ve been advised to “run the macro inside Sub Auto_Close”, but I have no idea what this means? (My VB skills are elementary.) Can anyone help?

Many thanks,
Sam

 

[SkipVought]

Hi,

Your code should...

UNPROTECT
you loop
PROTECT

Skip,

_[red][/red]

 

[SkipVought]

Hi,

Your code should...

UNPROTECT
your loop
PROTECT

Skip,

_[red][/red]

 

[combo]

First, after opening the workbook, protect the sheet again with parameter UserInterfaceOnly set to True (ThisWorkbook module):
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Name" or Index).Protect Password:="Password", UserInterfaceOnly:=True
End Sub
You can now freely change protected worksheet by code.

Secondly, your macro fires twice, you can consider disabling events:
Application.EnableEvents = False
For Each Cell In Target
...
Next Cell
Application.EnableEvents = True

combo

 

[cresbydotcom]

protection also plays havoc with copy and paste. Or did in the versions I used last time I tried it.

end users freak-out unless it is exactly like they always used it - hence macros are preferrable to formulae and protected sheets.

 

[Sam577]

So I should just enclose the code with the two words UNPROTECT and PROTECT? Like this?

Private Sub Worksheet_Change(ByVal Target As Range)
UNPROTECT
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("G:G").Column Then
Cells(.Row, "N").Value = Now()
End If
End With
Next Cell
PROTECT
End Sub

 

[SkipVought]

There is a specific syntax for PROTECT & UNPROTECT. It has the SHEET as an object. You may also need passwords or other parameters depending one the TYPE of sheet protection that is being used. Turn on your macro recorder to see the code required & check HELP

Skip,

_[red][/red]