CODEWITHSUNDEEP
excelvbaedit
Cornelis
Cornelis

Reputation: 435

Prevent User to edit range of cells manual (but allowing VBA to do)

I'd like to prevent the user from being able to editing a certain range of cells. But VBA should keep the ability to do this. So when a button in Excel is pressed VBA should be able make modifications in the Cell's values, while the user can't do this manually.

Just found a sollution in an other toppic which rather fits my demands: How to protect cells in Excel but allow these to be modified by VBA script

Only thing I'm still questioning is if this is alwso aplicable to a certain range instead of the whole workbook?

Upvotes: 1

Views: 8758

Answers (2)

Cornelis
Cornelis

Reputation: 435

Sollution

Worksheet.Protect "Password", UserInterfaceOnly := True

to Apply on a certain Range (I gues the folloing code should work...?):

Worksheet.Sheets("ControlSheet").Range("M5:N19").Protect "Password", UserInterfaceOnly :=True

Upvotes: 2

GijsA
GijsA

Reputation: 250

Protect the sheet with VBA:

Dim sh As Worksheet
Dim myPassword As String
myPassword = "password"

For Each sh In ActiveWorkbook.Worksheets
    sh.Protect Password:=myPassword
Next sh

Now, if you want to modify something with VBA you can unprotect them with:

Dim sh As Worksheet
Dim myPassword As String
myPassword = "password"

For Each sh In ActiveWorkbook.Worksheets
    sh.Unprotect Password:=myPassword
Next sh

And then protect them again afterwards.

Upvotes: 1

Related Questions