How to lock Excel cells in VBA?

Question

I have an Excel worksheet that acts like an application, with form control buttons allowing users to 'navigate' through records. First, Previous, Next & Last cycle appropriately through one of the worksheets records, displaying the values in my 'form' worksheet.

When users are not in Edit or Add Mode, I would like to lock the cells to prevent users from modifying contents.

I tried Range("A1:O24").Locked = True, but I am still able to type new values into the cells.

Anyone know how to accomplish this? I need my vba code to be able to assign new values to the cells as users 'navigate', but to prevent users from entering new values unless in Add or Edit mode.

have you seen [this](http://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868)? theres a commented out `'ws.Protect userinterfaceonly:=True` in the VBA solution which pretty much locks the user interface and still allows you to edit cells from VBA subs. — , Jun 14 '13 at 23:18
@mehow: Small word of warning though - the `UserInterfaceOnly` property is not saved with the file! Therefore, any macro will run into issue when you open a file - unless you use the Open event to re-set the property! — Peter Albert, Jun 15 '13 at 16:12

score 5 · Accepted Answer · answered Jun 14 '13 at 15:13

5

I believe the reason for this is that you need to protect a worksheet before cells actually become locked. All cells are formatted as locked as a default so what you really want to do is set the range that you don't want locked to Range().Locked = False and then set the worksheet to protected.

In the case that you want all cells locked all you have to do is set the worksheet to protected

answered Jun 14 '13 at 15:13

SELECTCOUNTSTAR

100
2
11

I see what you are saying with the .Protect now. This however seems to not be working for my situation :/ Protecting (or "locking") the cells prevents my vba code from changing cell values as users cycle through records. I need to prevent users form modifying cell values without being in Add/Edit, but allow my code to change the values as form control buttons are clicked and their events run. – Analytic Lunatic Jun 14 '13 at 16:10

score 3 · Answer 2 · answered Sep 18 '13 at 09:34

Search for your condition whether user was not in Edit or Add mode and then locking your range and finally protect your worksheet.

Let's say for example in one case, if you want to locked cells from range A1 to I50 then below is the code:

Worksheets("Enter your sheet name").Range("A1:I50").Locked = True
ActiveSheet.Protect Password:="Enter your Password"

In another case if you already have a protected sheet then follow below code:

ActiveSheet.Unprotect Password:="Enter your Password"
Worksheets("Enter your sheet name").Range("A1:I50").Locked = True
ActiveSheet.Protect Password:="Enter your Password"

How to lock Excel cells in VBA?

2 Answers2

Linked

Related