-
Jun 2nd, 2011, 03:42 PM
#1
Thread Starter
Member
[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: 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?
Last edited by arcanine; Jun 2nd, 2011 at 04:37 PM.
Reason: Change wording to remove the ads that were injected into my post.
-
Jun 2nd, 2011, 04:24 PM
#2
Re: [Excel] Change Event Target.Value
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 2nd, 2011, 04:44 PM
#3
Thread Starter
Member
Re: [Excel] Change Event Target.Value
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
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|