Executing code in a library as a result of a worksheet change

[Ian Wheaton]

I have a sub held in a module within a add in library (xla) called
calcForeign.

I want to execute this sub when a specific cell changes value. Heres my
code to spot the change on the worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$28" Then
Application.EnableEvents = False
'execute my sub
calcForeign

Application.EnableEvents = True
End If
End Sub

However excel will not find this sub - i get "compile error sub or
function not defined"

I can add a button to the sheet and assign the following macro
'C:\OrderIT\OrderITAddin.xla'!calcForeign
and it works OK

How do i reference a sub held in an xla Library?

Gratefull for any help

Regards

Ian

 

[Chip Pearson]

You need to create a reference from your xls file to your xla addin. In VBA,
open your Add-In, go to Tools, VBA Project Preferences, and give your XLA a
distinctive Project Name, like MyAddin. Save the add-in. Then open your XLS
in VBA, go to the Tools menu, choose References, and select your add-in's
project name (e.g., MyAddin) in the list. Put a check next to it. Once you
do this, you can call function in your XLA directly.

If you don't want to use a reference, you have to the Run command. E.g.,

Application.Run "AddinName.xla"!calForeign


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)