Solved

Modify Macro to Copy Target.Value on Delete

Posted on 2014-09-18
3
185 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 500 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

733 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