Equivalent of ActiveCell for sheet that is not ActiveSheet

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Dave Peterson]

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.)

 

[Guest]

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

Jerry

 

[Peter T]

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

 

[Guest]

Thank you for joining in. Fortunately, you correct observation does not
impact my objective.

Jerry

 

[Dave Peterson]

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

 

[Peter T]

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).Formula1
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

 

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

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]

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