Can't change value of cell with code in a protected sheet

Question

I have a password protected sheet, with some unlocked cells that user can chage.

Once the user changes any value, it automatically should make changes to other unlocked cells by vba code. This works fine if the sheet is unlocked, but not if it's protected.

example of code:

In Workbook_Open() I set UserInterfaceOnly attribute to TRUE:

Sheets("Sheet Name").Protect Password:="123456", UserInterFaceOnly:=True, Contents:=True

Sheet code: Set date.01 value into date.02 cell if date.01 changes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("date.01")) Is Nothing Then
    Worksheets("Sheet Name").Range("date.02") = Target
  End If
End Sub

cells "date.01" and "date.02" are unlocked.

Why can't I update them?

EDIT:

Is SelectionChange event the best option to change cell values? And is it ok to do the assignment like this:

Worksheets("Sheet Name").Range("date.02") = Target

I can see that the changes are applieD when the original cell get the focus back.

What I really want to do is to give a group of cells in different sheets the same value anytime any of them are changed by the user.

SOLVED.

My bad, I was using

Worksheet_SelectionChange

instead of

Worksheet_Change

I also had to use this to prevent any errors.

Application.EnableEvents = False
<CODE>
Application.EnableEvents = True

There was no need of using UserInterfaceOnly as all cells/ranges are unlocked.

Try `Sheets("Sheet Name").Protect Password:="123456", UserInterFaceOnly:=False, Contents:=True` — R3uK, Feb 13 '17 at 12:32
Same, it doesn't change. I forgot to say that the cell has a validation rule for dates. — giorgiline, Feb 13 '17 at 12:47
Does it throw any message? Add an error message for your validation rule, that way you'll know if the error is coming from it! — R3uK, Feb 13 '17 at 12:52
`Sheets("Sheet Name").Protect Password:="123456", UserInterFaceOnly:=True` The UserInterFaceOnly should be set to true. That means the protection does not affect your macros. http://stackoverflow.com/questions/125449/how-to-protect-cells-in-excel-but-allow-these-to-be-modified-by-vba-script — John Muggins, Feb 13 '17 at 13:20
UIO doesn't affect everything. In particular formatting doesn't always work under UIO=true. See here for more info http://stackoverflow.com/questions/24999377/userinterfaceonly-true-doesnt-seem-to-allow-vba-changes-to-conditional-formatt — Harley B, Feb 13 '17 at 15:48

score 0 · Answer 1 · answered Feb 13 '17 at 15:56

0

you can simply check it's something to do with protection.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("date.01")) Is Nothing Then

Sheets("Sheet Name").unProtect Password:="123456"

`Worksheets("Sheet Name").Range("date.02") = Target`
`Sheets("Sheet Name").Protect Password:="123456", UserInterFaceOnly:=True`

End if

End Sub

Do you apply validation through VBA

answered Feb 13 '17 at 15:56

Deb

121
1
1
12

That doesn't work neither. Validation is applied through application UI. – giorgiline Feb 13 '17 at 16:53

giorgiline · Accepted Answer · 2017-02-16T08:20:33.793

The event method to use is Worksheet.Change Event

https://msdn.microsoft.com/en-us/library/office/ff839775.aspx

And the code was like:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("date.01")) Is Nothing Then
    Application.EnableEvents = False
    Worksheets("Sheet1").Range("date.01") = Target
    Worksheets("Sheet2").Range("date.02") = Target 
    Worksheets("Sheet3").Range("date.03") = Target 
    Worksheets("Sheet4").Range("date.04") = Target 
    Worksheets("Sheet5").Range("date.05") = Target 
    Application.EnableEvents = True
  End If
End Sub

Can't change value of cell with code in a protected sheet

2 Answers2