Excel How to lock a cell without using macros, if possible.

Question

I have a solution implemented using a macro, but I want to remove it if I can. So here is the problem

I have a drop-down list implemented using a validation rule on a cell. I want this cell to be read-only depending on the value in the second cell on the same sheet.

I tried to block the use of another check, but this does not allow me.

Any idea?

Answer 1

I assume the data validation is in cell A2 , and the value you are checking is in cell A1

When you change the value of cell A1 to “Blah-blah,” the code will start and then block cell A2 . Please take a moment to read the comments in the code before you run it.

The code must be inserted in the code area of ​​the worksheet.

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.EnableEvents = False Dim mypassword As String, StringToCheck As String On Error GoTo Whoa '~~> Change the password here to protect/unprotect the sheet mypassword = "password" '~~> Change it to the relevant string with which you want to compare StringToCheck = "Blah Blah" If Not Intersect(Target, Range("A1")) Is Nothing Then '~~> Check for the cell value If Target.Value = StringToCheck Then '~~> Un-Protect the sheet ActiveSheet.Unprotect mypassword '~~> Lock the cell Range("A2").Locked = True '~~> Re-Protect the sheet ActiveSheet.Protect mypassword End If End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub 

Answer 2

You can prevent the user from entering new data using only Data Validation

EDIT: use the formula in the list

Thanks to the Head of Catering commentary, it's worth remembering that you can use formulas with Data Validation / List.

See his response to another thread to see it in action: fooobar.com/questions/1427950 / ...

Original post

Walkthrough for Excel 2007:

• Ribbon> Data> Data Validation
• Log in: Personal (or similar, my Excel is not in English sorry)
• Enter this formula in the field:
  • =IF(A1="",FALSE,TRUE)

Thus, you cannot enter a value in the cell if A1 is still empty.

Answer 3

Got it to work, but you can only set the value when it comes out in the drop-down menu

I used the name manager to set the name test to =IF(Sheet1!$A$1=1,"",Sheet1!$E$1:$E$5)

this means that if A1 is 1, I get nothing, and if A1 is something else, I get E1: E5

Then I set data validation to List , with source before =test
you can set the value only when test returns a list

When I do this, when A1 is 1, I get an empty list in the validation drop-down list, and I cannot change the value. If A1 is not 1, I get a list of E1: E5, and I can change the value

Answer 4

This is an old post, but I found a solution that will throw an error and prevent peeps from entering whatever they want (using Excel 2010), without using VBA.

In the user interface (SHEET1), I have the following fields:

• Field 1 is a yes / no validation drop-down list.
• Field 2 is a drop-down list of percent validation with values ​​of 1% - 100%.

What I need:

• When "Yes" is selected, I can select or enter 1% -100% in field 2. It is also "available."

• When “No” is selected, field 2 becomes gray and the user can no longer enter any random value.

How I did it:

• In field 2, I used conditional formatting (formula = 'SHEET2'! $ I $ 2 = "No") and set the font color and background color to gray.

• In SHEET2, I set the field (column I) to field 1 from SHEET1, so it is either "Yes" or "No" depending on what is selected in field 1 from SHEET1. Then I just added 99 other lines below it, setting them equal to the cell directly above it. Therefore, I have 100 lines of “Yes” or “No”.

• In SHEET2, I have another column (R) with the first cell value: = IF (I2 = "Yes", 0.01, 1)

• Immediately below it, I: = IF (I3 = "Yes", R2 + 0.01, 1)

• Then I pre-populate the values ​​down the lines below until I have 1% to 100% (when "Yes" is selected) and set the validation in the SHEET1 2 field based on these values. This allows me to manually enter the% value or select from the dropdown values ​​from 1% to 100%.

Results:

When I select "No", it selects field 2 and leaves% of what it was before, because it cannot change the displayed value without VBA. For example, it can show 30%. However, if the user sees it in gray and tries to enter some value in it, for example 31%, it will cause an error, since I set ERROR on the "Error Warning" tab in the Data Validation for this field. If they choose a drop-down list, they just see 100 options at 100%. This is the only drawback, but I think that I could just close everything except one field showing 100%.

When I select "Yes", it discards field 2, and I am free to choose from the values ​​of the list of additional orders between 1-100 again.

What do I need to do with the values:

For what I need to perform, I set some formula that I use based on 100% if "No" is selected and the field 2% if "Yes" is selected.