Ranges vs cells with VBA
 

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

Ranges vs cells with VBA
 

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
--
Wag more, bark less


"Brad" wrote:


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

Ranges vs cells with VBA
 

If you want to prevent a client from doing this to more than one cell, ensure
the target.count is equal to one.
--
http://www.ExcelHelp.us

888-MY-ETHER ext. 01781474



"Brad" wrote:

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
--
Wag more, bark less


"Brad" wrote:


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

Ranges vs cells with VBA
 

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 (MVP - Excel)


"Brad" wrote in message
...

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

Ranges vs cells with VBA
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Brad" wrote in message
...

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

Ranges vs cells with VBA
 

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.

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Brad" wrote in message
...

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

Ranges vs cells with VBA
 

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

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Brad" wrote in message
...

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

Ranges vs cells with VBA
 

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


Brad wrote:

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

--

Dave Peterson