I don't understand how an Add-In is supposed to detect and react to events in
other workbooks in the absence of event code in those workbooks. Please
elaborate.

Jerry

"Peter T" wrote:

Both the suggestions I made could be extended to work for UDF's in an Addin,
albeit with a lot more code.

The event driven method could be done with a collection WithEvents
workbooks(s), in turn holding a collection of activecells (only necessary to
maintain on required sheets). First time a UDF on a given sheet is called it
wouldn't find the workbook class or relevant activecell. It could return
"Pending", at the same time if necessary create the relevant WB withevents
class and set a sheet to be processed (activated & activecell stored) on the
next event, eg selection change. In future selection changes check if
current sheet is one that needs updating and if so add the new activecell.

In part similar for the dummy CF method, a lot less event code involved but
would still need the event to check a similar flag and add a dummy CF to
sheet. Obviously that means modifying the wb, ie a dummy CF on sheets that a
UDF will want to poll. Though they could be removed in the withevents wb
close or save event.

Only briefly explained but doable with a view to 'correcting' and returning
CF formulas on other sheets. Inevitably there would be calc-update delays at
times doing the necessary internal updates (not so much). But even
ordinarily the UDF's are unlikely to update without a nudge unless change is
triggered by a dependency, and that wouldn't include cells within the CF
formula of the UDF referenced cell.

Regards,
Peter T.


"Jerry W. Lewis" wrote in message
...
The function will live in an Add-In of general-purpose tools that are
available to the entire site, and so should be self-contained. Even my
own
use will be primarily for reviewing workbooks that I don't control and
therefore should not modify. Nor can I assume that the sheet of interest
has
ever been active in the current Excel session.

Jerry

"Peter T" wrote:

Without knowing your overall objective a couple of approaches you might
consider, to return the activecell on the non active sheet in a UDF

1. In Workbook_SheetSelectionChange Add the activecell to a public
collection, say gColCells, with key sheetname, after deleting the
previous
key/item.

Initialize the collection in the wb activate by looping worksheets and
adding each active cell. Plenty of error handling in case removing non
existent key, activechart etc. Recalc in sheet activate or deactivate.

Pass SheetName!cell-ref to the UDF and 'gColCells(cel.Parent.Name)'
should
refer to the activecell object on SheetName.

2 In cell A1 in sheets you are interested in, ie your CF sheets, add a
CF
with formula =A1, no format required.

Pass SheetName!cell-ref to the UDF and
sF= cel.Parent.Range("a1").FormatConditions(1).Formula 1
sAddr = Mid(sF, 2, 10)

sAddr should refer to the address of the activecell on SheetName

Recalc probably required, when & where depending on overall scenario

The CF could be in any cell, not necessarily A1, with the formula
referring
to itself, adjust the UDF code to return from same.

Regards,
Peter T

"Jerry W. Lewis" wrote in message
...
Thank you for joining in. Fortunately, you correct observation does
not
impact my objective.

Jerry

"Peter T" wrote:

Hi Jerry,

If, and I'm guessing, your objective is to evaluate a CF formula
that
contains relative address(es), even if you could get the activecell
on
the
non-active sheet wouldn't you still have a problem. Wouldn't
Evaluate
then
use addresses from the active sheet.

Regards,
Peter T

"Jerry W. Lewis" wrote in message
...
Without activating the sheet, how can I determine which cell on
that
sheet
that will become the ActiveCell once the sheet is activated?

I am trying to write a UDF that will be called from Excel
(therefore
changing selections is not an option) that will return .Formula1
from
the
conditional format of a cell whose reference is passed to the
function.
The
problem is that all relative addressing in .Formula1 is
automatically
converted to A1 style relative to the cell that would be
ActiveCell if
the
cell's .parent were ActiveSheet. John Walkenbach's suggestion
http://j-walk.com/ss/excel/odd/odd07.htm
only works on ActiveSheet, but could easily be modified if I could
identify
the equivalent of ActiveCell for a sheet that is not ActiveSheet.

Jerrry