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

Any workbook can trap Application wide events, or events in 'other'
Workbooks or Sheets. In all cases need to instanciate the class and
permanently store its object reference. In the case of Workbook & Worksheet
can store multiple instances of class in a collection or array.

Add three classes as follows and at the top of each module add the
Withevents

clsXLevents
Public Withevents xlApp as Excel.application
clsBookEvents
Public Withevents mWB as Excel.workbook
clsSheetEvents
Public Withevents mSH as Object

In the middle dropdown select of say the wb class select 'mwb', then look
for the events in the right dropdown.

I think for your purposes a collection of Workbook events for those wb's to
be added as flagged when required.

' normal module

Public gColBooks as collection

Sub SetBook(wb as workbook)
Dim cls as clsBookEvents
if gColBooks is nothing then set gColBooks = new collection

' code to check if wb is already in the collection, else
set cls = New clsBookEvents
set cls.mwb = wb
mColBooks.Add cls, wb.Name

End sub

If this is new to you it may take quite a while to get it all working
reliably along the lines I had in mind, so there need to be a good reason to
do it. Assuming it all works as best it can I'm not sure your UDF's will
work entirely as you hope. Eventually though they should at least return
those CF formulas but probably not fully updated until full re-calc.

If your objective is merely for periodically reviewing CF formulas in other
books, I would have thought a simpler macro approach would be the way to go.
Ie, activate sheets as necessary and dump results to cells.

Regards,
Peter T

"Jerry W. Lewis" wrote in message
...
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