Excel Sheet Validation
Hello !!
I have requirement to validate for Null value in excel sheet. Below code is used in one of my application (Which compares Column A & B entry. As of now, it validates for A Column <= Column B), but fails to validate for Null, which is creating problem. I have tried by setting IgnoreBlank = False, but no success. Please help me out !! to validate for Null value as well in either Column A 0r B. Private Sub Workbook_Open() Dim intCnt As Integer Dim intTotal As Integer intTotal = 10 For intCnt = 1 To intTotal Range("A" & intCnt).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="0", Formula2:="=B" & intCnt .IgnoreBlank = True .InCellDropdown = False .ErrorTitle = "A Vs B Qty Error" .ErrorMessage = "A Quantity cannot be greater than B Quantity." .ShowInput = True .ShowError = True End With Next End Sub |
Excel Sheet Validation
VBA has two functions ISEMPTY() and ISNULL(). Add a test if ISNULL(Selection ) = true then "raajkul" wrote: Hello !! I have requirement to validate for Null value in excel sheet. Below code is used in one of my application (Which compares Column A & B entry. As of now, it validates for A Column <= Column B), but fails to validate for Null, which is creating problem. I have tried by setting IgnoreBlank = False, but no success. Please help me out !! to validate for Null value as well in either Column A 0r B. Private Sub Workbook_Open() Dim intCnt As Integer Dim intTotal As Integer intTotal = 10 For intCnt = 1 To intTotal Range("A" & intCnt).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="0", Formula2:="=B" & intCnt .IgnoreBlank = True .InCellDropdown = False .ErrorTitle = "A Vs B Qty Error" .ErrorMessage = "A Quantity cannot be greater than B Quantity." .ShowInput = True .ShowError = True End With Next End Sub |
Excel Sheet Validation
If you are trying to force a user to make an entry in a cell, there is no
form of data validation that will do that. -- Regards, Tom Ogilvy "raajkul" wrote: Hello !! I have requirement to validate for Null value in excel sheet. Below code is used in one of my application (Which compares Column A & B entry. As of now, it validates for A Column <= Column B), but fails to validate for Null, which is creating problem. I have tried by setting IgnoreBlank = False, but no success. Please help me out !! to validate for Null value as well in either Column A 0r B. Private Sub Workbook_Open() Dim intCnt As Integer Dim intTotal As Integer intTotal = 10 For intCnt = 1 To intTotal Range("A" & intCnt).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="0", Formula2:="=B" & intCnt .IgnoreBlank = True .InCellDropdown = False .ErrorTitle = "A Vs B Qty Error" .ErrorMessage = "A Quantity cannot be greater than B Quantity." .ShowInput = True .ShowError = True End With Next End Sub |
Excel Sheet Validation
IsNULL is useless for checking if a cell is empty or not. Isempty is the
correct check. -- Regards, Tom Ogilvy "Joel" wrote: VBA has two functions ISEMPTY() and ISNULL(). Add a test if ISNULL(Selection ) = true then "raajkul" wrote: Hello !! I have requirement to validate for Null value in excel sheet. Below code is used in one of my application (Which compares Column A & B entry. As of now, it validates for A Column <= Column B), but fails to validate for Null, which is creating problem. I have tried by setting IgnoreBlank = False, but no success. Please help me out !! to validate for Null value as well in either Column A 0r B. Private Sub Workbook_Open() Dim intCnt As Integer Dim intTotal As Integer intTotal = 10 For intCnt = 1 To intTotal Range("A" & intCnt).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="0", Formula2:="=B" & intCnt .IgnoreBlank = True .InCellDropdown = False .ErrorTitle = "A Vs B Qty Error" .ErrorMessage = "A Quantity cannot be greater than B Quantity." .ShowInput = True .ShowError = True End With Next End Sub |
All times are GMT +1. The time now is 07:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com