IsEmpty () Excel function in vba not working as expected
I want to create a worksheet that has a number of properties, eg. columns that only accept numbers and columns that only accept values from dropdowns. I have created a VBA script that works almost perfect, but I have one problem that I cannot solve, which seems to be function related IsEmpty()
.
At the moment only values from the dropdown list can be entered in columns C, D and HI, and my error message ("Select a value from the dropdown list") is selected differently (including if values are inserted), Calling the function IsEmpty()
allows you to delete values without mistakes.
In the range Value_Columns
again I can only enter values and they are formatted correctly. If I delete a value in one cell, the function IsEmpty()
behaves normally and the value is cleared. However, if I select a range of cells (for example I5:I10
) and click Delete, the following error message appears:
The entry must be a number
This conflicts with columns C, D, and H, where deleting a range of cell contents does not throw an error message.
I see no reason why this behavior seems inconsistent. Can anyone please help?
Sub Worksheet_Change(ByVal Target As Range)
Dim Industry_Column As Range
Dim Proposition_Column As Range
Dim Status_Column As Range
Dim Value_Columns As Range
Set Industry_Column = Range("C5:C500")
Set Proposition_Column = Range("D5:D500")
Set Status_Column = Range("H5:H500")
Set Value_Columns = Range("I5:W500")
If Not IsEmpty(Target) Then
If Not Application.Intersect(Target, Industry_Column) Is Nothing Then
If IsError(Application.Match(Target, Worksheets("Drop Down Lists").Range("A2:A6"), 0)) Then
Application.EnableEvents = False
Application.Undo
MsgBox "ERROR - Please select value from drop-down list"
Application.EnableEvents = True
End If
ElseIf Not Application.Intersect(Target, Proposition_Column) Is Nothing Then
If IsError(Application.Match(Target, Worksheets("Drop Down Lists").Range("C2:C6"), 0)) Then
Application.EnableEvents = False
Application.Undo
MsgBox "ERROR - Please select value from drop-down list"
Application.EnableEvents = True
End If
ElseIf Not Application.Intersect(Target, Status_Column) Is Nothing Then
If IsError(Application.Match(Target, Worksheets("Drop Down Lists").Range("E2:E6"), 0)) Then
Application.EnableEvents = False
Application.Undo
MsgBox "ERROR - Please select value from drop-down list"
Application.EnableEvents = True
End If
ElseIf Not Application.Intersect(Target, Value_Columns) Is Nothing Then
If Not IsNumeric(Target) Then
Application.EnableEvents = False
Application.Undo
MsgBox "ERROR - Entry must be a number"
Application.EnableEvents = True
Else
Target.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
End If
End If
End If
End Sub
source to share
If you look at the definition of IsEmpty () https://msdn.microsoft.com/en-us/library/office/gg264227.aspx it indicates that it is working on an expression
When you do If Not IsEmpty(Target) Then
VBA implicitly casts it If Not IsEmpty(Target.Value) Then
and Range.Value is an expression. Thus, an autonomous range can be considered an expression.
But in your case, implicit expression to expression doesn't work. You will need to create a background system that looks like if all cells are empty.
Submit and replace IsEmpty()
with the IsRangeEmpty()
following:
' Return if a range is empty
Private Function IsRangeEmpty(ByVal rng As Range) As Boolean
Dim cell As Range
' for each cell in the range
For Each cell In rng
' if a cell is not empty
If Not IsEmpty(cell) Then
' return "not empty"
IsRangeEmpty = False
Exit Function
End If
Next
' Here all cells are empty
IsRangeEmpty = True
End Function
source to share