Announcement

Collapse
No announcement yet.

Worksheet_Change, Merged Cells, and the Delete Key

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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":

    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)


    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!

  • #2
    What about using the Sendkeys command?

    Code:
     
     SendKeys.Send("{DELETE}")
    The Only Dumb Question Is A Question Not Asked.

    Comment


    • #3
      Hi,
      have you tried,,
      Code:
      On Error Resume Next
      If Not Intersect(Target, Range("RTIndRTO", "RTentRTO", "RTRedRTO", "RTVndRTO")) Is Nothing Then
      If the solution helped please donate to RSPCA

      Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

      Comment


      • #4
        Have not tried either route. I'll give both a shot and report back!

        Comment


        • #5
          On Error Resume Next worked like a charm, thank you!

          (did not try SendKeys because this one solved the issue)

          Comment


          • #6


            Donít over use on error resume next but it is good for known and tricky errors. If possible you should handle the errors with syntax
            If the solution helped please donate to RSPCA

            Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

            Comment

            Working...
            X