1

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?

Community
  • 1
  • 1
Cornelis
  • 445
  • 3
  • 11
  • 27
  • 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 Oct 28 '15 at 13:17
  • 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 Oct 28 '15 at 13:27
  • 1
    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 Oct 28 '15 at 13:28
  • Didn't remember of UserInterfaceOnly parameter, my bad. – Vincent G Oct 28 '15 at 13:35
  • Possible duplicate of [How to protect cells in Excel but allow these to be modified by VBA script](http://stackoverflow.com/questions/125449/how-to-protect-cells-in-excel-but-allow-these-to-be-modified-by-vba-script) – mauek unak Oct 28 '15 at 13:35
  • yes... but would like to know how to apply this on just a certain range – Cornelis Oct 28 '15 at 14:04

2 Answers2

2

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
Cornelis
  • 445
  • 3
  • 11
  • 27
1

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.

GijsApeldoorn
  • 250
  • 2
  • 10