Creating an add-in that utilizes the Worksheet_Change function on a new sheet. RRS feed

  • Question

  • Hello all,

    I am currently trying to create an add-in that can be used in any new sheet1 that I make. I can get it to work if I copy and paste the code into the new sheet but it doesn't work when I load the Add-in.

    The add-in consists of a Module, 2 sheets, and a workbook.  I've saved the Add-in as an .xlam file and it has no problems loading.

    Here's the worksheet change code:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim iRow As String
        If Target.Column = 8 Then
            If Target = "" Then
                iRow = Target.Row
                Call CheckList("C" + iRow)
            End If
        End If

    End Sub


    Thursday, December 22, 2016 5:34 PM

All replies

  • I suppose it's better for me to ask Is it possible to make add-on like that? If not is there a way replicate it?
    Thursday, December 22, 2016 6:55 PM
  • You can't execute that code in a different file. But your AddIn can have code and catch the change event of the application.

    Copy the code below into the code module "ThisWorkbook" of your AddIn.


    Option Explicit
    Dim WithEvents App As Application
    Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      If Not Target Is Nothing Then
        MsgBox "You changed " & Target.Address(0, 0)
      End If
    End Sub
    Private Sub Workbook_Open()
      Set App = Application
    End Sub

    Thursday, December 22, 2016 7:50 PM
  • Thanks for the response although I already have a way determine what's being checked off on my list. I'm disappointed that I can't move over event code from the sheets in my add-in but what can ya do I guess. That and I need to reference the same add-in in the main project in order to use it's functions.
    Thursday, December 22, 2016 8:51 PM