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

Unless the sheet is newly inserted, the ActiveCell will become the last
ActiveCell at the time the sheet was de-activated.

In worksheet code (for Sheet1 for example) enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhereWasI = Target.Cells(1, 1).Address
End Sub


In a standard module, enter:

Public WhereWasI As String
Sub trace_it()
MsgBox (WhereWasI)
End Sub

Activate any cell on Sheet1, then activate any other worksheet, then run
trace_it.
It will report what will become the ActiveCell if Sheet1 is re-actvated.
--
Gary''s Student


"Jerry W. Lewis" wrote:

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

Thanks for the quick reply, but unless I have missed your point, I don't
think it solves my problem.

The function I am trying to write needs to read conditional formats in
workbooks that I have no control over. Therefore a solution that requires
those workbooks to have pre-existing event code is not an option.

MS clearly has access to this 'non-active ActiveCell', since it converts
..Formula1 relative to it, but I am beginning to fear that exposing it to VBA
is yet another omission from the object model. Anyone?

Jerry

"Gary''s Student" wrote:

Unless the sheet is newly inserted, the ActiveCell will become the last
ActiveCell at the time the sheet was de-activated.

In worksheet code (for Sheet1 for example) enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhereWasI = Target.Cells(1, 1).Address
End Sub


In a standard module, enter:

Public WhereWasI As String
Sub trace_it()
MsgBox (WhereWasI)
End Sub

Activate any cell on Sheet1, then activate any other worksheet, then run
trace_it.
It will report what will become the ActiveCell if Sheet1 is re-actvated.
--
Gary''s Student


"Jerry W. Lewis" wrote:

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

From VBA's help, Activcell belongs to the application or the activewindow.

It sure looks like you have to go to the trouble of activating the sheet to me.

Is there a reason you can't turn off events, activate the sheet you want,
remember the activecell and go back to where you were, and turn events back on?

(Yeah, there has to be--else you wouldn't have made it part of the
requirements.)


Jerry W. Lewis wrote:

Thanks for the quick reply, but unless I have missed your point, I don't
think it solves my problem.

The function I am trying to write needs to read conditional formats in
workbooks that I have no control over. Therefore a solution that requires
those workbooks to have pre-existing event code is not an option.

MS clearly has access to this 'non-active ActiveCell', since it converts
.Formula1 relative to it, but I am beginning to fear that exposing it to VBA
is yet another omission from the object model. Anyone?

Jerry

"Gary''s Student" wrote:

Unless the sheet is newly inserted, the ActiveCell will become the last
ActiveCell at the time the sheet was de-activated.

In worksheet code (for Sheet1 for example) enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhereWasI = Target.Cells(1, 1).Address
End Sub


In a standard module, enter:

Public WhereWasI As String
Sub trace_it()
MsgBox (WhereWasI)
End Sub

Activate any cell on Sheet1, then activate any other worksheet, then run
trace_it.
It will report what will become the ActiveCell if Sheet1 is re-actvated.
--
Gary''s Student


"Jerry W. Lewis" wrote:

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

--

Dave Peterson

Functions called from Excel are not capable of selecting or activating
anything.

Jerry

"Dave Peterson" wrote:

From VBA's help, Activcell belongs to the application or the activewindow.

It sure looks like you have to go to the trouble of activating the sheet to me.

Is there a reason you can't turn off events, activate the sheet you want,
remember the activecell and go back to where you were, and turn events back on?

(Yeah, there has to be--else you wouldn't have made it part of the
requirements.)


Jerry W. Lewis wrote:

Thanks for the quick reply, but unless I have missed your point, I don't
think it solves my problem.

The function I am trying to write needs to read conditional formats in
workbooks that I have no control over. Therefore a solution that requires
those workbooks to have pre-existing event code is not an option.

MS clearly has access to this 'non-active ActiveCell', since it converts
.Formula1 relative to it, but I am beginning to fear that exposing it to VBA
is yet another omission from the object model. Anyone?

Jerry

"Gary''s Student" wrote:

Unless the sheet is newly inserted, the ActiveCell will become the last
ActiveCell at the time the sheet was de-activated.

In worksheet code (for Sheet1 for example) enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhereWasI = Target.Cells(1, 1).Address
End Sub


In a standard module, enter:

Public WhereWasI As String
Sub trace_it()
MsgBox (WhereWasI)
End Sub

Activate any cell on Sheet1, then activate any other worksheet, then run
trace_it.
It will report what will become the ActiveCell if Sheet1 is re-actvated.
--
Gary''s Student


"Jerry W. Lewis" wrote:

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

--

Dave Peterson

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

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

Ahh. I didn't notice it was part of a UDF. Sorry.

Jerry W. Lewis wrote:

Functions called from Excel are not capable of selecting or activating
anything.

Jerry

"Dave Peterson" wrote:

From VBA's help, Activcell belongs to the application or the activewindow.

It sure looks like you have to go to the trouble of activating the sheet to me.

Is there a reason you can't turn off events, activate the sheet you want,
remember the activecell and go back to where you were, and turn events back on?

(Yeah, there has to be--else you wouldn't have made it part of the
requirements.)


Jerry W. Lewis wrote:

Thanks for the quick reply, but unless I have missed your point, I don't
think it solves my problem.

The function I am trying to write needs to read conditional formats in
workbooks that I have no control over. Therefore a solution that requires
those workbooks to have pre-existing event code is not an option.

MS clearly has access to this 'non-active ActiveCell', since it converts
.Formula1 relative to it, but I am beginning to fear that exposing it to VBA
is yet another omission from the object model. Anyone?

Jerry

"Gary''s Student" wrote:

Unless the sheet is newly inserted, the ActiveCell will become the last
ActiveCell at the time the sheet was de-activated.

In worksheet code (for Sheet1 for example) enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhereWasI = Target.Cells(1, 1).Address
End Sub


In a standard module, enter:

Public WhereWasI As String
Sub trace_it()
MsgBox (WhereWasI)
End Sub

Activate any cell on Sheet1, then activate any other worksheet, then run
trace_it.
It will report what will become the ActiveCell if Sheet1 is re-actvated.
--
Gary''s Student


"Jerry W. Lewis" wrote:

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

--

Dave Peterson


--

Dave Peterson

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

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

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

Interestingly, this exposes another hole in the object model. If Sheet1 is
not ActiveSheet when the "selection" changes on Sheet1 (as when multiple
sheets are are selected), then the Worksheet_SelectionChange event on Sheet1
will not fire.

Jerry

"Gary''s Student" wrote:

Unless the sheet is newly inserted, the ActiveCell will become the last
ActiveCell at the time the sheet was de-activated.

In worksheet code (for Sheet1 for example) enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhereWasI = Target.Cells(1, 1).Address
End Sub


In a standard module, enter:

Public WhereWasI As String
Sub trace_it()
MsgBox (WhereWasI)
End Sub

Activate any cell on Sheet1, then activate any other worksheet, then run
trace_it.
It will report what will become the ActiveCell if Sheet1 is re-actvated.
--
Gary''s Student


"Jerry W. Lewis" wrote:

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

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