1

I have solution implemented using the macro but I want remove it if I can. So here the issue

I have drop-down implemented using validation rule on a cell. I want that cell to be readonly depending on the value in second cell on same sheet.

I tried locking the using another validation but it don't allow me.

Any Idea?

Improve this question
2
  • how have you tried to prevent user from entering value? It should work using a formula Commented Aug 14, 2012 at 14:04
  • more detail in this question would really help Commented Aug 14, 2012 at 18:21

4 Answers 4

Reset to default
6

I am assuming that the data validation is in cell A2 and the value which you are checking is in Cell A1

When you change the value of cell A1 to "Blah Blah" the code will run and then lock cell A2. Please take a moment to read through the comments in the code before you actually run it.

The code has to be pasted in the worksheet code area.

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for quick reply but I like I already solution using macro(vba) I want remove to make work with .xlsx and work on tablets
So you don't want a VBA solution. is that what you are saying?
Then why mention macros in your post title and in the tags :)
i have macro i want to remove it :)
Ok. I have amended your post title. :) But just to let you know, AFAIK, you cannot do it without macros.
6

You can prevent a user entering new data using only Data Validation

EDIT: use formula in list

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

See his answer to another thread to see this in action: https://stackoverflow.com/a/11902463/138938

Original post

Walkthrough for Excel 2007:

  • Ribbon > Data > Data Validation
  • Authorize : Personnalized (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

Improve this answer

8 Comments

I already validation rule to create drop-down on the cell. it doesn't allow me to add the second validation to same cell
@user1598124: Can you use and OR and AND statement in your validation rule formula to meet your 2 validation requirements?
@user1598124: I don't know any standard way to add a second validation and I didn't find any dropdown solution without Data Validation. At last, I can't see how we could solve your issue without VBA
@Scott Holtzman I don't see any option in validation for doing that
@user1598124, yes, I just tried to play with my idea, but it hasn't worked like I thought it might.
|
2

Got it to work, but you can only actually set the value when it works out to a dropdown

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

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

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

When I do this, when A1 is 1, I get an empty list in my validation dropdown, and I can't change the value. If A1 is not 1, I get my list of E1:E5, and I can change the value

Improve this answer

2 Comments

+1 Interesting approach, but I tested it and it certainly doesn't lock the cell: when A1 is 1, you don't get a dropdown, but you can actually type in any value at all and the validation won't complain.
This is similar to Head of Catering solution (see his comment to my answer) but his solution is more straightforward and might work. Nice suggestion though
0

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.

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

  • Field 1 is a Yes/No Validation Dropdown list.
  • Field 2 is a percent validation Dropdown list with values of 1% - 100%.

What I needed:

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

  • When "No" is selected, field 2 becomes "grayed out" and user can no longer enter some random value.

How I did it:

  1. For field 2, I used a Conditional Formatting (formula of ='SHEET2'!$I$2="No") and set it to gray out the font and background.

  2. In SHEET2, I set a field (column I) to equal field 1 from SHEET1, so it's either "Yes" or "No" depending on what's selected in field 1 from SHEET1. I then simply added 99 other rows beneath it, setting them equal to the cell immediately above it. So I have a 100 rows of "Yes" or "No".

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

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

  5. I then prefill the values down the rows below it until I have 1% throuh 100% (when "Yes" is selected) and set the validation on SHEET1's field 2 to be based on these values. This allows me to manually enter a % value, or select from the dropdown values 1% through 100%.

Results:

When I select "No", it grays out field 2, and leaves the % whatever it was before because it can't change the displayed value without VBA. For example, it might still show 30%. However, if the user sees it grayed out and attempts to type some value in it, like 31%, it'll throw an error since I set an ERROR on the "Error Alert" tab in Data Validation for this field. If they choose the dropdown, they just see like 100 options of 100%. This is the only downside, but I guess I could have just blanked out everything except 1 single field showing 100%.

When I choose "Yes", it ungrays field 2, and I am free to choose from the doprdown list values between 1-100 again.

What I needed to do with the values:

For whatever it is I need to accomplish, I set some formula I'm using based on 100% if "No" is selected and field 2's % if "Yes" is selected.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.