I have a module which is creating a sheet. It builds a worksheet with a series of tables. 

I'd like to add a feature that uses the OnChange event for a cell to validate that the user entered a decimal. The following code does this *If I can just inject into the new worksheet*. That's the only thing I can't figure out. 

Given 's' is the current Worksheet we've just created is there any way to inject the following code into the sheet code module of 's'?

```
Private Sub Worksheet_Change(ByVal Target As Range)
Const CELL_ADDRESS = "$R$4:$AQ$500" 
If Not Application.Intersect(Target, Range(CELL_ADDRESS)) Is Nothing Then
   If Not IsNumeric(Target.Value) Then
      MsgBox "Please enter numbers only", vbCritical, "Invalid Entry"
      Target.Value = vbNullString
   End If
End If 
End Sub

```

---

EDIT: Showing the chosen solution to the problem (chose Wedge's solution). 

(Answer:) We will add a public function to the Addin which we will call from the Template and therefore all sheets created from the template. 

Using a template and copying it will allow us to have custom code built into new sheets without having to change security settings. 

Calling a public function allows us to make modifications to the sheet without putting the protected password in the sheet's code.

(Public Function Call inside the sheet)

```
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim ws As Worksheet
Set ws = wb.ActiveSheet

Application.Run "numberaddin.Validate_Input", wb, ws, Target

End Sub

```

(Public Function built into Addin which will be called by the sheet when a user modifies data.)

-- All this function does is make sure our cells store only numbers AS NUMBERS and with formatting. Any non-value text becomes a 0 in the cell. This works even if the user copy-pastes the data.

```
Public Function Validate_Input(wb As Workbook, ws As Worksheet, r As Range)
    CELL_ADDRESS = Cells(1, 2).Value ''''we'll use the locked Cell B1 to specify the Validation Range
    Dim rCell As Range
    Dim eCell As Range
    Dim numErr As Boolean

    numErr = False

    Set rCell = Range(CELL_ADDRESS)

    If Not Application.Intersect(rCell, r) Is Nothing Then
       ActiveSheet.Protect Password:="pw", UserInterfaceOnly:=True
       Application.EnableEvents = False
       For Each eCell In rCell.Cells
           If Not eCell Is Nothing And eCell.Locked = False And Not Application.Intersect(eCell, r) Is Nothing Then
              If IsNumeric(eCell.Value) = False Or IsEmpty(eCell.Value) = True Or eCell.Value <> eCell.Value + "0" Then
                 If Not IsNumeric(eCell.Value) Then
                    numErr = True
                 End If
                       eCell.Value = Val(eCell.Value)
              End If
                 eCell.Interior.Color = RGB(255, 255, 153)
                 eCell.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
              If eCell.Value > 1000000 Then
                 eCell.Columns.AutoFit
                 eCell.ColumnWidth = eCell.ColumnWidth * 1.2
              End If
          End If
       Next eCell
       Application.EnableEvents = True
       ActiveSheet.Protect Password:="pw", UserInterfaceOnly:=False
    End If

    If numErr = True Then
       MsgBox "Only numbers are allowed here.", vbCritical, "Invalid Entry"
    End If

End Function

```