Prevent edit of cells

[Guest]

Dear experts,

I've a worksheet with a macro which when users fill-in column A, auto
current dates and times appear in columns B & C respectively. I want after
the dates and times filled into cells in columns B & C, they cannot be edited
anymore. I tried to lock the cells by "Protection" but a run-time error
dialog box appeared. Is there a way to solve this problem? Please advise.

Thanks in advance.

 

[Gary]

ActiveSheet.Unprotect Password:="password"

(your current code)

ActiveSheet.Protect Password:="password"
Else: MsgBox "The cell can not be edited"
End If
End Sub

Protect the sheet with the password you have mentioned in the code.

hope this helps
Thanks.

 

[JP]

Hello,

1. Unlock and unprotect all cells (Ctrl-A, then Ctrl-1, Protection
tab, uncheck 'Locked' and 'Hidden')
2. Select the formula cells in columns B&C, hit Ctrl-1, Protection
tab, check 'Locked' and 'Hidden')
3. Protect your worksheet

An added bonus: the formulas will be hidden so nobody can see what
calculations you are making.


HTH,
JP

 

[Guest]

Hi JP,

Thanks for your reply.

However, may be you misunderstood my question. I know how to protect cells
and a worksheet. My problem is, when I protect the cells in column B & C
which are used to show auto current dates and times by a macro, the macro
will not work and the dates and times will not be shown in protected cells.

Thanks & regards.

 

[Guest]

Hi Gary,

I'll try the code. Thanks.

ActiveSheet.Unprotect Password:="password"

(your current code)

ActiveSheet.Protect Password:="password"
Else: MsgBox "The cell can not be edited"
End If
End Sub

Protect the sheet with the password you have mentioned in the code.

hope this helps
Thanks.

 

[JP]

Ah ok, what you need to do is protect the worksheet with your macro
like this:

Sheets(1).Protect Password:="Secret", UserInterFaceOnly:=True


The "userinterfaceonly" property will allow your macro to edit a
protected worksheet.


HTH,
JP