Solved How to write Macro for Protect the cells in Excel 2013

October 17, 2016 at 08:39:52
Specs: Windows 7

Need to prepare time sheet with protection.
1. User should not enter time manually between A1:B5, it has to be done by clicking macro button(Macro already written for that).

' Button1_Click Macro
' Keyboard Shortcut: Ctrl+q
ActiveCell.FormulaR1C1 = Now

End Sub

2. Need to protect cell A1:B5 with password XXX.
User should not edit those cell range manually. It should be done by only pressing Macro button.

See More: How to write Macro for Protect the cells in Excel 2013

Report •

October 17, 2016 at 09:34:14
✔ Best Answer
It is not clear to me if you want to allow users to enter data in cells other than A1:B5.
If so, you need to Unlock the other cells before you Protect the sheet.

1 - Click in the box above the Row numbers to select all cells.
2 - In the Home...Format...Format Cells dialog box, chose the Protection tab.
3 - Uncheck the Locked option - This will unlock all cells
4 - Select A1:B5
5 - Under Home...Format choose Lock Cell - This will Lock (but not Protect) A1:B5
6 - Under Home...Format choose Protect sheet, set your options and enter a password

A1:B5 will now be protected from user entry.

Attach the following code to your button. As written, it will place Now in the active cell if the cell is within A1:B5 and present a message to the user if outside of that range.

Sub TimeInCells()
 If Not Intersect(ActiveCell, Range("A1:B5")) 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

However, since the worksheet password needs to be included in the macro as clear text, you should hide and protect the VBA code. If you don't, savvy users will be able to find the worksheet password. If you follow the instructions found at the following site, you will be able to hide and protect the VBA code itself.

Let me know if you have any questions.

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

Report •
Related Solutions

Ask Question