Data Validation Formula changes to VBA Code

Closed
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 7, 2015 at 07:19 PM
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 9, 2015 at 03:38 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 9, 2015 at 09:41 AM
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
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 9, 2015 at 12:43 PM
Please help me to understand the code
Thanks
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768 > smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017
Nov 9, 2015 at 01:32 PM
'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
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 9, 2015 at 02:00 PM
Thanks for explanation.
In the end I perceived that I have to set Range as variable then call it for procedure
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768 > smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017
Nov 9, 2015 at 03:18 PM
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
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 9, 2015 at 03:38 PM
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