Conditional Format Lock Cell

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• May 17, 2008

• #2

Perhaps. How is the value entered?

lenze

 

Upvote 0

A

Ark68

Well-known Member

Joined

Mar 23, 2004

Messages

4,665

Office Version

1. 365
2. 2016

Platform

1. Windows

• May 17, 2008

• #3

As a result of an "if" formula.
So in essence, if the formula is positive, the user will not be permitted to edit the cell ... it's locked. If negative, the user can edit the cell ... unlocked.

 

Upvote 0

V

VBA Noob

Active Member

Joined

Apr 27, 2006

Messages

353

• May 17, 2008

• #4

Try an event macro > right click sheet tab > select view code > paste in the below.

If A1 formula returns 10 then the cell is locked.

Code:

Private Sub Worksheet_Calculate()
Dim Rng As Range
Set Rng = Range("A1")

    Select Case Rng
        Case Is = 10
            Rng.Locked = True
        Case Else
            Rng.Locked = False
    End Select
End Sub

HTH

VBA Noob

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• May 17, 2008

• #5

Since there is a formula in the cell, what cell should be locked? I assume the cell with formula is already locked. Am I wrong? Provide specifics.

lenze

 

Upvote 0

A

Ark68

Well-known Member

Joined

Mar 23, 2004

Messages

4,665

Office Version

1. 365
2. 2016

Platform

1. Windows

• May 18, 2008

• #6

Hi Lenze,

This is a normally unlocked cell (A1) allowing a user to enter information. However, if a certain value is encountered in another worksheet cell (B2), the user no longer should be permitted to edit (enter information) in the unlocked field (A1). That value when encountered in B2 needs to lock the cell A1 to prevent editting.

So, formula in cell A1 is if(b2="NO","","ok"). So, if locking a cell was available through conditional formatting, I would apply something like ... if A1 = "NO" then (Lock cell)

Hmmm?

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• May 18, 2008

• #7

I still don't get it. If you have
=IF(B2="NO","","OK")
in A1, why can someone enter info in A1?

lenze

 

Upvote 0

A

Ark68

Well-known Member

Joined

Mar 23, 2004

Messages

4,665

Office Version

1. 365
2. 2016

Platform

1. Windows

• May 18, 2008

• #8

I think now maybe I'm getting confused.

I believe the only reason why I want to lock the cell is to prevent a) the user selecting it, and b) overwriting what the formula has defined.

If the formula in A1 assigns B2 a value of NO, I don't want the user to be able to select and overwrite the value assigned by the formula. However, with any other value, the user is permitted to select and change.

 

Upvote 0