Data Validation Formula changes to VBA Code

Closed
Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Hi
The Below formula
=IF(OR(D2="Best",D2="Worst"),FALSE,TRUE)
used in Data Validation for Column E
I would like to request that it could changes to VBA Code because there are two formulas which merged for Column E and each condition is differ and may confuse user what he has done
in this manner that ranges are set D2 to D1000
if user enter Best or Worst in Column D Range then he is not allowed to enter any value in Column E
I have also tried this but failed

If (Me.Range("D11:D510").Value = "Best") Or (Me.Range("D11:D510").Value = "Worst") Then
rngTarg.Select
MsgBox "No permission, as you enter <" & Me.Range("D11:D510").Value & ">", vbCritical, "Access Denied"
rngTarg.ClearContents
End If
Thanks

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you need to understand the code to see what it is doing

This is a range
"D11:D510"
you cannot check range as "D11:D510").Value = "Best"
0
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
Please help me to understand the code
Thanks
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768 >
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017

'Me is a keyword. Copied from excel help related to ME
'The Me keyword behaves like an implicitly declared variable. It is automatically available to every procedure in a class module. When a class can have more than one instance, Me provides a way to refer to the specific instance of the class where the code is executing. Using Me is particularly useful for passing information about the currently executing instance of a class to a procedure in another module

'Range("D11:D510").Value = "Best" is checking if range value is equal to BEST. Think for a second. On excel sheet, would you ever say for a group of cells a singel value. I think you would say answer is NO. So what to do, would depends on what you want to check. You may want to check of each cell in range has that value or at least once cell has it etc.

So these lines will never work. you have to examine one cell at a time in a range
If (Me.Range("D11:D510").Value = "Best") Or (Me.Range("D11:D510").Value = "Worst") Then

I am not too sure what exactly and how exactly you are planning to use, Based on your previous questions, i thought you had the solution that you seek.
0
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1 >
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

Thanks for explanation.
In the end I perceived that I have to set Range as variable then call it for procedure
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768 >
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017

What you are exactly trying to do. From what I have understood from your previous questions
1. You want to validate input in column B and E by checking value in column D
so if a cell in column D has "best" or "worst", then for that row, you want to clear out values from column B and Column E
Or is it some thing else
0
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1 >
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

Yes I want to validate input from column E only and if a cell in column D has "best" or "worst", then for that row, you want to clear out value from Column E only
0