Worksheet_Change, Merged Cells, and the Delete Key

  • Hi folks,
    Really struggling with this one and hoping someone has ideas on how to do this or what I'm missing.

    I'm trying to code a series of checks when values on a worksheet change, and based on what changed do other things. I've got that part down fine until a delete key is pressed. From research, I understand the problem is that the deletion is a merged cell, which trips up the code.

    I've seen some writeups that suggest you need to go through each cell to bypass the delete error, but I am trying to minimize how much code I have to write. For example, I do the same action if any one of four merged cell ranges changes to "Yes":

    [VBA]If Not Intersect(Target, Range("RTIndRTO")) Is Nothing Or Not Intersect(Target, Range("RTentRTO")) Is Nothing Or Not Intersect(Target, Range("RTRedRTO")) Is Nothing Or Not Intersect(Target, Range("RTVndRTO")) Is Nothing Then)[/VBA]

    This works fine, until the dreaded delete hits. So I've been trying to intercept it with an Application.OnKey "{Delete}", "SubName" line, but results have been inconsistent and certainly not doing what I'm trying to do.

    If there's another way to capture the delete key and bypass the rest of my code in the worksheet_change sub that would be ideal, but I'm not aware of a way to do that.

    Any advice would be greatly appreciated! I will have several compound IF statements like the one above, so an efficient way to dodge the delete error when applied to a merged cell is pretty necessary. If nothing else, if I can just disable deleting as a last resort, I'd do that.

    Thanks in advance!

  • What about using the Sendkeys command?


    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!