CODEWITHSUNDEEP
vbaexcel
Dante Revealed
Dante Revealed

Reputation: 61

Excel Add .xlam module inject code into new sheet

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

Upvotes: 0

Views: 414

Answers (2)

Sam
Sam

Reputation: 5731

First of all you must enable the "Trust access to the VBA project object model" setting in the Trust Center.
After that you'll have to write something like this:

Sub AddModule()
    Dim Module As VBComponent
    Dim ModuleString As String

    ModuleString = "Sub Test()" & vbCrLf & _
                   "    Msgbox(""Test"")" & vbCrLf & _
                   "End Sub"

    Set Module = Workbooks(2).VBProject.VBComponents.Add(vbext_ct_StdModule)
    Module.CodeModule.AddFromString ModuleString

End Sub

Obviously, you will have change the workbook reference and the ModuleString. Also be careful with the trust change. It is there for a reason.

Upvotes: 2

Wedge
Wedge

Reputation: 1826

It's not exactly what you were asking, but I would think that you could just create a hidden "template" sheet with the code you want in it (there is an xlVeryHidden option you can use to keep the template sheet from being unhidden from the UI even). Then instead of creating a new worksheet, you create a copy of that "template sheet", which should copy over the sheet VBA code with it.

Upvotes: 1

Related Questions