Results 1 to 3 of 3

Thread: [RESOLVED] [Excel] Change Event Target.Value

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Resolved [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?
    Last edited by arcanine; Jun 2nd, 2011 at 04:37 PM. Reason: Change wording to remove the ads that were injected into my post.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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:
    1. If Target.Value(1) <> "" Then
    test all cells in range
    vb Code:
    1. for each c in target
    2.    if c.value <> "" then
    ignore if more than 1 cell in target
    vb Code:
    1. if target.cells.count =1 then
    2.   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

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    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
  •  



Click Here to Expand Forum to Full Width