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)
Best Answer
You can use Worksheet_Calculate on a trigger sheet.
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.