Data Validation Formula changes to VBA Code

Closed

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

Excel vba validation formula1
Number to words in excel without vba - Guide
Enable vba in excel - Guide
Vba case like - Guide
Excel date format dd.mm.yyyy - Guide
Excel free download - Download - Spreadsheets

1 response

Answer 1 / 1

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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"

smuneeb Posts 67 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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > smuneeb Posts 67 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.

smuneeb Posts 67 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1 > rizvisa1 Posts 4478 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

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

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

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel

Similar discussions

Subscribe To Our Newsletter!