[RESOLVED] [Excel] Change Event Target.Value

Hello,

I'm trying to determine if the changed cell on a worksheet is blank or not. I use the following code in the worksheet's Change event,

Code:

---

If Target.Value <> "" Then
    ...
End If

---

This works great, unless I use the delete key to clear a cell. When I do this, I get a Run-time error '13': Type mismatch. When I step through the code I added the following watch:

Code:

---

Target.Value

---

The type is shown as the following:

Code:

---

Variant/Variant(1 to 2, 1 to 1)

---

Does this mean the value is a two dimension variant array? Why? More importantly, how do I check for this, so my code doesn't break?

if you use delete key on single cell then your code will work correctly
if you have a multiple cell a\range selected then delete you get type mismatch error

there are several ways to avoid the error, depending on the result you want from your code
just test the first cell in range (however many cells in range)

vb Code:

If Target.Value(1) <> "" Then

test all cells in range

vb Code:

for each c in target
   if c.value <> "" then

ignore if more than 1 cell in target

vb Code:

if target.cells.count =1 then
  If Target.Value <> "" Then

Got it. Yes, I'm using merged cells. Now it makes sense what it's doing. I actually used the following work-around. It's probably not the most elegant/robust, but it's working so far:

Code:

---

If TypeName(Target.Value) <> "Variant()" Then
    ...
End If

---