The following VBA module works in one workbook (thanks to the help from this forum). But as I copy the module from my first workbook and paste it to a new one, it will not activate. That is, the “Worksheet_Change” event does not seem to trigger in the new workbook. Here’s the code. (I do not understand the formatting conventions despite earlier posts. I apologize.)
Option Explicit Function CleanCode(Rng As Range) Dim strTemp As String Dim n As Long For n = 1 To Len(Rng) Select Case Asc(Mid(UCase(Rng), n, 1)) Case 48 To 57, 65 To 90 strTemp = strTemp & Mid(UCase(Rng), n, 1) End Select Next CleanCode = strTemp End Function Private Sub Worksheet_Change(ByVal Target As Range) Dim ColA As String Dim ColB As String Dim CellA As Range Dim CellB As Range Set CellA = ActiveCell.Offset(-1, 0) Set CellB = ActiveCell.Offset(-1, 1) If Len(CellA.Value) > 0 Then ColA = CleanCode(CellA) ColB = CleanCode(CellB) If (ColA <> ColB) Then MsgBox CellB CellA.Select End If End If End Sub
A secondary wish is to make the module work for any (active) worksheet in the workbook. But my main concern is why the module works in one workbook and not in another.