(Solved) – module copied vba code will not work in new workbook

  • by

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
    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
    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.

Leave a Reply

Your email address will not be published. Required fields are marked *