How to lock Excel cells in VBA?

1
votes

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.

2
vbaexcelprotectedoffice-2007
have you seen this? 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. – user2140173
@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

2 Answers

5
votes

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

3
votes

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"