Copy-paste doesn't trigger worksheet_change

Question

Here is what I need to do: When I have written something into a cell in the sheet, my Worksheet_Change code should check if the cell contains certain characters and then replace those characters. That part of the code is working fine.

However there is a slightly odd behavior. Here's the code so far:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Target.Value = Replace(Target.Value, "ß", "ß")

        MsgBox "This is the value: " & Target.Value
    End Sub

What's happening is that when I have the characters that need to be changed in my clipboard (using Ctrl+C). When I double-click onto the cell, paste the characters into the cell using Ctrl+V, and then press Enter, the code works just fine, the characters are changed. (Note: Without the double-click, you can't see the cursor.)

However, if I just go to the cell with my arrow keys and paste over whatever else is in the cell, nothing happens. I suspect the Worksheet_Change isn't even triggered, or else it would at least display my MsgBox.

(I don't know if it's relevant to this, but i am using Excel 2010 in a Mac)

Answer 1

You can use Worksheet_Calculate on a trigger sheet.

• Create a new sheet which only contains a link to the range you want to watch (i.e. =Sheet1!A1:B3)
• Add a macro called Worksheet_Calculate to your trigger sheet
• Now whenever any date in the linked range changes a recalculation of your trigger sheet gets triggered which in turn sets off your macro

This also works when the user enters a formula in the corresponding cell and one of the predecessors changes.

With manual calculation the recalculation of the trigger sheet and the macro only happen when the user presses F9.

Answer 2

Always use EnableEvents property when working with Worksheet_Change event. For more details link

  Private Sub Worksheet_Change(ByVal Target As Range)

 On Error GoTo err_rout


    Application.EnableEvents = False

    If Target.Count > 1 Then Exit Sub

    Target.Value = Replace(Target.Value, "ß", "ß")

    MsgBox "This is the value: " & Target.Value

err_rout:
    Application.EnableEvents = True
End Sub