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

0
votes

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.

2
vbaexcel
Try Sheets("Sheet Name").Protect Password:="123456", UserInterFaceOnly:=False, Contents:=True – R3uK
Same, it doesn't change. I forgot to say that the cell has a validation rule for dates. – giorgiline
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
Sheets("Sheet Name").Protect Password:="123456", UserInterFaceOnly:=True The UserInterFaceOnly should be set to true. That means the protection does not affect your macros. stackoverflow.com/questions/125449/… – John Muggins
No validation error is thrown in any case. – giorgiline

2 Answers

0
votes

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

0
votes

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