CODEWITHSUNDEEP
excelvba
Thomas
Thomas

Reputation: 149

Including "ThisWorkbook" code in Excel add-in

I am working on creating a VBA Excel-Add in. I require the use of the "Workbook_SheetChange" Sub. Is it possible to include this code as a portion of the add-in so that all workbooks where the add-in containing user defined functions are used have access to the code that executes on a sheet change?

The file is being saved as a .xla add-in. However "Workbook_SheetChange" will not work from a module, and must be in the ThisWorkbook code section of a file. Thus it is not carried over with my add-in currently.

Upvotes: 4

Views: 3133

Answers (2)

Damon Lim
Damon Lim

Reputation: 1

This solve my problem

code add into the .xlam

dim currentworkbook as Workbook
set currentworkbook = activeworkbook

then set all your variable that want to link to current workbook as currentworkbook.

then will be functioning well.

dont use thisWorkbook, as it will locate your .xlam file as thisWorkbook instead of the one you really want to run it.

Upvotes: -1

Dick Kusleika
Dick Kusleika

Reputation: 33145

You need a custom class module in your add-in that has an Application variable declared WithEvents. I usually call my class module CAppEvents.

Private WithEvents mxlApp As Application

Public Property Set App(xlApp As Application)

    Set mxlApp = xlApp

End Property

Now in the top left drop down of the code pane (Ctrl+F2), you have mxlApp. Select that and tab over to the top right drop down and choose SheetChange. You'll get a stub like this.

Private Sub mxlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

If any cell on any sheet in any open workbook changes, this event will fire. You'll want to add some code to make sure it's a sheet you care about. You don't get the workbook name in the arguments, but you can use Sh.Parent to access the workbook.

The only other thing you need is to create the class. In a standard module

Public gclsAppEvents As CAppEvents

Sub Auto_Open()

    Set gclsAppEvents = New CAppEvents
    Set gclsAppEvents.App = Application

End Sub

Making the variable public makes it stay in scope while you're add-in is loaded. Any sub in a standard module named Auto_Open will run when the workbook is first opened.

Upvotes: 5

Related Questions