Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Modify Macro to Copy Target.Value on Delete

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
-Polak
Asked:
-Polak
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
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
 
-PolakAuthor Commented:
Oh..... Was wondering why I couldn't get that to work on my own, that makes sense (annoyingly). Thanks Glenn.
0
 
Glenn RayExcel VBA DeveloperCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now