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

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

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

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