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

0
votes

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?

2
excelvbaedit
To prevent the user to modify the cell, protect them (and the worksheet). When you need to modify them with VBA, just unprotect them, do you modifications and re-protect them after. – Vincent G
Then I would end up protecting and unprotecting endles times, due my code contains a lot of parts where the user/VBA take turns on editing the Workbook. thanks though! – Cornelis
No other choice, sorry. But you can make two Sub to protect and Unprotect the cells that you call at the start and at the end of your VBA processing. – Vincent G
Didn't remember of UserInterfaceOnly parameter, my bad. – Vincent G
Possible duplicate of How to protect cells in Excel but allow these to be modified by VBA script – mauek unak

2 Answers

2
votes

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

1
votes

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.