CODEWITHSUNDEEP
excelvbaexcel-addins
Defi
Defi

Reputation: 9

In Excel VBA, how to run a worksheet event from add-in to active workbook

I like to run code from an Excel add-in to the active workbook (xlsx). The code should apply some formatting to the active cell of the active workbook, when activated in the menu.

How can I achieve this?

Normally you can realize this by using the worksheet_change event in the active workbook, but this requires:

I like to have this applied via an add-in, independent of the workbook and thus without the need to insert code in this workbook and to make this a xlsm workbook first.

Upvotes: 1

Views: 1467

Answers (2)

Defi
Defi

Reputation: 9

Thanks both, seems I have new insights here I can test and try out.

Upvotes: -1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

You can catch WorksheetChange events from one worksheet in another (class) module, but in this case you probably rather use Application.SheetChange as above (code in ThisWorkbook module in addin):

'Create an object to catch events
Dim WithEvents ExcelApp As Excel.Application

'Assign object when addin opens
Private Sub Workbook_Open()
    Set ExcelApp = Application
End Sub

'Handle sheet changes in all workbooks
Private Sub ExcelApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rng As Range
    'Do some validation...
    If Sh.Parent.Name = "Book1" And Sh.Name = "Sheet1" Then
        'Ensure correct range
        'Note: changes may occur in many cells at a time (delete, paste, ...)
        Set rng = Intersect(Target, Workbooks("Book1").Worksheets("Sheet1").Range("A1:B2"))
        If Not rng Is Nothing Then
            rng.Interior.ColorIndex = vbRed 'Example
        End If
    End If
End Sub

Upvotes: 2

Related Questions