?
Solved

Modify Macro to Copy Target.Value on Delete

Posted on 2014-09-18
3
Medium Priority
?
188 Views
Last Modified: 2014-09-18
Hi Experts,

I have attached a macro that needs a small modification, within the StandardMode and ManualMode sheets the below code copies and pastes cells M2-M4 across both sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
    If CascadeSelections Then Exit Sub
    Select Case Target.Address
        Case "$M$2"
            CascadeSelections = True
            Sheets("ManualMode").Range("M2").Value = Target.Value
        Case "$M$3"
            CascadeSelections = True
            Sheets("ManualMode").Range("M3").Value = Target.Value
        Case "$M$4"
            CascadeSelections = True
            Sheets("ManualMode").Range("M4").Value = Target.Value
    End Select
    CascadeSelections = False
End Sub

Open in new window

However, the macro does not execute when an user presses the "delete" key on cell m2,m3,or m4 in either of the given sheets. Is it possible to make it delete inputs across both sheets if a user presses the delete key?
EE-Safe-Copy-Macro.xlsm
0
Comment
Question by:-Polak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40331117
The reason the Worksheet_Change event isn't capturing the [Delete] action is because the Target.Address for that spans four columns and isn't being considered in the Select Case section.  For example, if you delete the Region value, the Target.Address is "$M$2:$P$2".

So, try this instead:


Private Sub Worksheet_Change(ByVal Target As Range)
    If CascadeSelections Then Exit Sub
    Select Case Left(Target.Address, 4)
        Case "$M$2"
            CascadeSelections = True
            Sheets("StandardMode").Range("M2").Value = Target.Value
        Case "$M$3"
            CascadeSelections = True
            Sheets("StandardMode").Range("M3").Value = Target.Value
        Case "$M$4"
            CascadeSelections = True
            Sheets("StandardMode").Range("M4").Value = Target.Value
    End Select
    CascadeSelections = False
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:-Polak
ID: 40331149
Oh..... Was wondering why I couldn't get that to work on my own, that makes sense (annoyingly). Thanks Glenn.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40331197
Indeed, that is annoying.  I would think the Target.Address would be the same regardless of the action or selection.  Another good reason to avoid merged cells, though! :-)

-Glenn
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question