Locking rows based on value in cells

[Former Member]

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

[Former Member · Accepted solution]

Unsure about your example and code, as it seems to be referencing column 7 in the code, but the stat is in column 5. That aside, I would write the macro like this for the sample data provided:

Option Explicit
Private Sub LockingRow()
'Checks lock status and relocks to Allows VB changes
If ActiveSheet.ProtectContents Then
    ActiveSheet.Unprotect Password:="test"
    ActiveSheet.Protect Password:="test", UserInterfaceOnly:=True
End If
Dim ca, Cell As Range
Set ca = ActiveSheet.UsedRange
'Check used range column 5 for all values that match criteria
For Each Cell In ca.Columns(5).Cells
    If Cell.Value = 1 Then
        Range("a" & Cell.Row & ":d" & Cell.Row).Locked = True
    Else
        Range("a" & Cell.Row & ":d" & Cell.Row).Locked = False
    End If
Next Cell
End Sub

[Former Member · Accepted solution]

Hello Ryan,

I just used the code you provided into an AFTER_REFRESH event. And it worked.

Thanks a lot for your help!

Zaire

[dinesh_veluru]

Hi GZ,

Native protect and unprotect sheet for epm reports will have inconsistence behaviour.

please follow the below steps.

1)Go to Review tab in the excel and remove the protection in both workbook and sheet.

2) protect the epm worksheet by using Options -> sheet options - > protection---- protect Active Worksheet

3)  try below for unlocking and locking the epm worksheet.

Function after_workbook_open()

prot

after_workbook_open = True

End Function

Sub prot()

Dim api As New EPMAddInAutomation

api.SetSheetOption ActiveWorkbook.Worksheets("test"), 300, False, "pass"

.

.

.  code

.

.  
api.SetSheetOption ActiveWorkbook.Worksheets("test"), 300, True, "pass"

End Sub

Thanks,

Dinesh.V

[Shrikant_Jadhav]

Hi,

Put code in "AFTER_REFRESH"  function , write code to first unprotect shhet then add code , lock sheet.

1) AFTER_REFRESH

2) Unlock sheet

3) Your original code

4) Lock sheet

Shrikant 

[Former Member]

Hello Shrikant,

Thanks for your reply.

I did what you suggested but it has the same behavior.

Function AFTER_REFRESH(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

               AFTER_REFRESH = True

            Else

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

                 AFTER_REFRESH = True

            End If

        Next

    End If

    ActiveSheet.Protect "test"

End Function

Could you please let me know if I'm missing something or doing wrong?

Thanks a lot & Best Regards,

Zaire

[Shrikant_Jadhav]

Hi ,

Use EPM sheet protection:  - Options -> sheet options - > protection

May be helpful ...

Shrikant