Ranges vs cells with VBA

[Brad]

If I delete each cell in a range manually - I do not get hung up in the
below "if statement". If try to "clearcontents" to the entire range - it
doesn't recognize the isEmpty(Target.value) (I believe). What options do I
have...


If Target.Column = 2 And Not IsDate(Target.Value) And Not
IsEmpty(Target.Value) Then
MsgBox ("Inputs have to be dates")
Target.ClearContents
End If

 

[Brad]

Okay, if I disable the events and clear the area and then enable the events
the problem goes away. How can I handle the situation if the user manually
tries to clear all the cells at once (because then I still get hung up in the
original if statement.

Sub ClearSingle()
Application.EnableEvents = False
shtSingleDeposits.Range("B4:C8").ClearContents
shtInput.Range("InpSingleInd") = "No"
Application.EnableEvents = True
End Sub

 

[galimi]

If you want to prevent a client from doing this to more than one cell, ensure
the target.count is equal to one.

 

[Rick Rothstein]

IsEmpty does not exist to check if a cell is empty or not... it is used to
determine if a variable is initialize or not. You could test whether the
length of the contents of the cell are zero or not. Try this...

If Target.Column = 2 And Not IsDate(Target.Value) And Len(Target.Value) > 0
Then

Your newsreader will probably word wrap the above single line of code.

 

[Rick Rothstein]

Of course, if I had bothered to check before I posted my response, I would
have seen that VBA appears to have extended the functionality of IsEmpty to
cells in a spreadsheet... IsEmpty does, in fact, work for checking if a cell
is empty or not. However, if the cell contains a formula, even if the result
of that formula is the empty string (""), IsEmpty will report False. The Len
function check I posted will report 0 (empty string) for this situation.

 

[JLGWhiz]

Of course, this all depends on whether you are looking for empty cells or
zero value cells. If you want absolutely empty, then IsEmpty will do that.

 

[Charles Williams]

IsEmpty requires a Variant, so what is happening is that under the covers
the Range is being coerced to a variant and then IsEmpty will check the
first cell in the resulting variant array contained in the implied variant.

If the range is multi cell then you need a different strategy (looping,
Countif ..) to check for IsEmpty.
Also note that a not-yet calculated cell returns Empty ...

regards
Charles

 

[Dave Peterson]

You could throw up your hands and quit if the number of cells is greater than 1.

If target.cells.count > 1 then exit sub

or you could loop through each cell in the range to inspect.

dim rngToInspect as range
dim myIntersect as range
dim myCell as range

set rngtoinspect = me.range("b:b")

set myintersect = intersect(target,rngtoinspect)

if myintersect is nothing then
exit sub
end if

for each mycell in myintersect.cells
if isempty(mycell.value) then
'it's empty
else
if isdate(target.value) then
'it's a date
else
'not a date
end if
end if
next mycell