How to write macro for protected excel sheet?

November 16, 2016 at 17:42:17
Specs: Windows 7
Hi,

I'm trying to write a macro with below codings. Facing some difficulties, please help me to proceed further.

Requirement: Need to allow the user to edit objects & format cells in a protected cell(All cells). Did the changes in "Review>protect sheet>Allow all users of this work sheet to>Format cells/Edit objects"

Objective of this macro is "Multiple users will make start(A column)/end time(B column)".

Not let the user to make any changes in A& B column(Both are protected).

Its working fine in the first instance, Once i click start/end time "changes in "Review>protect sheet>Allow all users of this work sheet to>Format cells/Edit objects" going back to same position that users can not make any modifications.

Sub Button1_Click()
If Not Intersect(ActiveCell, Range("A:B")) Is Nothing Then
ActiveSheet.Unprotect Password:="XXX"
ActiveCell.FormulaR1C1 = Now
ActiveSheet.Protect Password:="XXX"
Else: MsgBox "This is not the correct cell."
End If
End Sub


See More: How to write macro for protected excel sheet?

Report •

#1
November 17, 2016 at 08:26:48
Did you Unlock the cells that you want the users to be able to modify?

By default, all cells are Locked, but you don't notice the Locked state because it doesn't really matter unless the sheet is protected.

Once you protect the sheet, cells cannot be altered unless you have Unlocked them prior to protecting the sheet.

If you only need to protect Columns A & B try this:

1 - Select all cells and Unlock them via the Format-Protection tab
2 - Select Columns A & B and Lock them via the Format-Protection tab
3 - Protect the sheet with whatever options you need.

You should also hide and protect your VBA code. If you don't, sophisticated users will be able to see it and learn the password.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 22, 2016 at 05:58:10
Hi,

I need to lock only A&B column. That I already did. And other cells remains open to the users

Here the difficulty is, if I write macro with above codings, I can not allow the users to make any modifications ( for ex. Users unable to insert comment in any of the cell).

While protecting the cell, I ticked "edit objects/format cells". After that, once macro runs protection getting unlocked & locked. Later " edit objects/format cells" options un-ticked.

Hope this clarifies.


Report •
Related Solutions


Ask Question