Skip to Content
0
Former Member
Feb 27, 2015 at 09:06 PM

Locking rows based on value in cells

336 Views

Hello all,

I'm trying to lock some rows based on a value in other cell, for example:

. A B C D E 1 Costcenter Account 2016.01 2016.02 stat 2 abc 123 1000 4000 1 3 abc 456 2000 5000 -1 4 abc 789 3000 6000 1

If stat = 1, then lock the row from costcenter fo 2016.02.

I'm using the next code:

Option Explicit

Private Sub Locking row (ByVal Target As Range)

Dim ca As Range

ActiveSheet.Unprotect "test"

If Target.Column = 7 Then

For Each ca In Target.Cells

If ca.Value = 1 And ca.Column = 7 Then

Range("a" & ca.Row & ":d" & ca.Row).Locked = True

Else

Range("a" & ca.Row & ":d" & ca.Row).Locked = False

End If

Next

End If

ActiveSheet.Protect "test"

End Sub

This code works fine in a normal worksheet but it's not working in an EPM Input Schedule.

I notice that when locking the EPM sheet through Excel menu, once the input schedule is refreshed, a window request for a password.

Then, I made a test lockig just from EPM menu and it did not work neither.

Do you know other way to achieve what I'm trying to do? Do you know if I'm missing something?

Thanks a lot for your help,

Best Regards,

Zaire