• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lock cells based on a condition [SOLVED]

ultros1234

New Member
Hi folks --


I need a little help. I'm setting up a spreadsheet that several people will add data to, and I'm trying to set some strict data validation rules. Column H asks, "Is this customer feedback?" possible values yes/no, and Column I asks "Is this a risk issue?" also yes/no.


Columns J-M are feedback related, and I want them to be locked UNLESS column H = "Yes"

Columns N-P are risk related, and I want them to be locked UNLESS column I = "Yes"


I dug up some VBA online that seems like a simple way to solve the problem. Pasted here:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Range("H3").Value = "Yes" Then

Range("J3:M3").Locked = False

Else

Range("J3:M3").Locked = True

End If

End Sub

When I use this VBA, then the J3:M3 cells are locked by default. When I change H3 value to "yes," I get the following error: "Run-time error '1004': Unable to set the Locked property of the range class."


Debugging points to this line of the code:

Range("J3:M3").Locked = False


Any ideas?
 
Last edited by a moderator:

SirJB7

Excel Rōnin
Hi, ultros1234!

Is the worksheet protected? If so, unprotect it first, do the job and protect it again.

Regards!
 

Sam Mathai Chacko

Active Member
The error that you see is because your sheet is protected, and in protected mode, you cannot change the Locked property of cell(s). As mentioned in the previous post, you will need to unprotect the sheet. Having said that, you don't need to run a line to unprotect it, do the job, and then protect it again.


Just run this once after unprotecting your sheet. The password argument of course is optional. You can completely remove that along with the comma(,) that precedes.

Code:
Sub ProtectMySheetFirst()


    Me.Protect userinterfaceonly:=True, Password:=""


End Sub
After you've ran the above code ONCE, you can simply use

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


    Me.Range("J3:M3").Locked = (Ucase(TRIM(Me.Range("H3").Value)) <> "YES")


End Sub
I just added the Ucase and TRIM methods just to cover one or two potential problems. The trim will take care of any leading or trailing space the user may type in (unless you've used a validation for Yes/No). The Ucase will bypass VBA's default text comparison mode, ie, binary.


For more information on text comparison in VBA, follow this link http://msdn.microsoft.com/en-us/library/8t3khw5f(v=vs.80).aspx
 
Last edited by a moderator:

ultros1234

New Member
Thanks Sam! Your method worked. But I'm encountering another problem, which I'm sure is easy, but I rarely touch VBA.


That method will check only row #3, but I need it to perform this check on all rows. So I tried changing the second part of the code to this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Me.Range("J3:M9999").Locked = (UCase(Trim(Me.Range("H3:H9999").Value)) <> "YES")


End Sub
But it doesn't like me. What am I doing wrong?


Thanks!
 
Last edited by a moderator:

Luke M

Excel Ninja
The problem is that this:

Code:
Me.Range("H3:H9999").Value
No longer refers to a single cell/value like it did before.

Let's try modifying the change macro to this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim xRow As Long
 
  xRow = Target.Row
 
  If Not (Intersect(Target, Range("H3:H1000")) Is Nothing) Then
  'changed something in col H
  Range(Cells(xRow, "J"), Cells(xRow, "M")).Locked = (UCase(Trim(Cells(xRow, "H").Value)) <> "YES")
  End If
  'Since it's possible to change both col h and col I, we'll use separate If statements
  'rather than an If...else statement
  If Not (Intersect(Target, Range("I3:I1000")) Is Nothing) Then
  'changed something in col I
  Range(Cells(xRow, "N"), Cells(xRow, "P")).Locked = (UCase(Trim(Cells(xRow, "I").Value)) <> "YES")
  End If
End Sub
 
Last edited:

ultros1234

New Member
Thanks Luke,


Getting closer, but one more snag:


Your solution does apply to the whole range, and it's nice that it's doing its thing for both of my two conditions now.


But the locking only kicks in once you type something in the cell (in column H or I). If I leave column H or I blank (or, I presume, if I closed the sheet and re-opened it regardless of the content of H or I), then I can still edit the other columns.
 

Luke M

Excel Ninja
I think if we set the starting condition of all the cells to be protected, that would fix this problem? You only want something to become unlocked if H or I gets changed to "yes". Does that work?
 

ultros1234

New Member
Thanks Luke,


Yes, that fixed the problem. Just one more thing! This VBA only works if I manually run the first function (Sub ProtectMySheetFirst) before I start to use the worksheet. If I close it and reopen it fresh, it gives me the same Run-time error '1004'.


But I want to hand this worksheet off to other folks to do data entry. So I want it to have this behavior every time it's opened. Is it possible to have it auto-magically run that first function upon opening the file?
 

Luke M

Excel Ninja
In the ThisWorkbook module of the VBE, put code similar to this:

Code:
Private Sub Workbook_Open()
Sheet1.ProtectMySheetFirst
End Sub
Note, that Sheet1 object may need to change. It needs to be the "code name" of the worksheet module where the code is stored. The code name is visible in the Project Explorer, and will be the name not enclosed in parenthesis.
 
Last edited:

Luke M

Excel Ninja
Sweet, then the code I wrote should be good as is. It will run whenever the workbook is opened.
 

ultros1234

New Member
When I re-open the workbook, I get the error I mentioned above. Only if I manually re-run ProtectMySheetFirst does the error go away.
 

SirJB7

Excel Rōnin
Hi, ultros1324!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 

Luke M

Excel Ninja
Try changing the one macro to this:

Code:
Sub ProtectMySheetFirst()
Me.Unprotect Password:=""   'Just in case protection already applied
Me.Protect userinterfaceonly:=True, Password:=""

End Sub
 
Last edited:

SirJB7

Excel Rōnin
Hi, ultros1234!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Lock cells based on a condition - example risk feedback data (for ultros1234 at chandoo.org).xlsm


I only split the unprotection and protection instructions in 2 procedures and added their calls in the worksheet change event code as below:

Code:
Option Explicit

Sub UnProtectMySheetFirst()
Me.Unprotect Password:=""   'Just in case protection already applied
End Sub

Sub ProtectMySheetLast()
Me.Protect UserInterfaceOnly:=True, Password:=""
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRow As Long

UnProtectMySheetFirst

xRow = Target.Row

If Not (Intersect(Target, Range("H3:H1000")) Is Nothing) Then
'changed something in col H
Range(Cells(xRow, "J"), Cells(xRow, "M")).Locked = (UCase(Trim(Cells(xRow, "H").Value)) <> "YES")
End If
'Since it's possible to change both col h and col I, we'll use separate If statements
'rather than an If...else statement
If Not (Intersect(Target, Range("I3:I1000")) Is Nothing) Then
'changed something in col I
Range(Cells(xRow, "N"), Cells(xRow, "P")).Locked = (UCase(Trim(Cells(xRow, "I").Value)) <> "YES")
End If

ProtectMySheetLast

Just advise if any issue.


Regards!
 
Last edited by a moderator:

SirJB7

Excel Rōnin
Hi, ultros1234!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 

ravi.kasg

New Member
Getting error wherein the following is highlighted in yellow in the code
Range(Cells(xRow, "J"), Cells(xRow, "M")).Locked = (UCase(Trim(Cells(xRow, "H").Value)) <> "YES")
 
Top