This works fine in all Functions I've written but throws an 'Object
required' in a Sub I'm writing:

Dim R as Range
Set R = Application.Caller

If Application.Caller is invalid in a Sub, then what expression DO you
use in a Sub to return the cell the user was on when he launched the
Sub?

Thanks much.

***

You can use ActiveCell (or maybe Selection, but Selection will include all the
selected cells).

And you can use application.caller in Subroutines--for instance, for instance if
you started the sub by clicking on a button from the forms toolbar (or any
control (I think) on that Forms toolbar.

Or a shape from the Drawing toolbar....

wrote:

This works fine in all Functions I've written but throws an 'Object
required' in a Sub I'm writing:

Dim R as Range
Set R = Application.Caller

If Application.Caller is invalid in a Sub, then what expression DO you
use in a Sub to return the cell the user was on when he launched the
Sub?

Thanks much.

***

--

Dave Peterson

This property returns information about how Visual Basic was called, as shown
in the following table.

Caller Return value
A custom function entered in a single cell retirms
A Range object specifying that cell

A custom function that is part of an array formula in a range of cells returns
A Range object specifying that range of cells

An Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro returms
The name of the document as text

A macro set by either the OnDoubleClick or OnEntry property returns
The name of the chart object identifier or cell reference (if applicable) to
which the macro applies

The Macro dialog box (Tools menu), or any caller not described above returns
The #REF! error value



" wrote:

This works fine in all Functions I've written but throws an 'Object
required' in a Sub I'm writing:

Dim R as Range
Set R = Application.Caller

If Application.Caller is invalid in a Sub, then what expression DO you
use in a Sub to return the cell the user was on when he launched the
Sub?

Thanks much.

***

Caller doesn't work in the context you describe. It can work in subs
though: for example if you call a sub from a (forms) button on a sheet or
from a click on a shape then Caller is the name of the button/shape.

Sounds like you need to look into either Selection or ActiveCell.

Tim

wrote in message
...
This works fine in all Functions I've written but throws an 'Object
required' in a Sub I'm writing:

Dim R as Range
Set R = Application.Caller

If Application.Caller is invalid in a Sub, then what expression DO you
use in a Sub to return the cell the user was on when he launched the
Sub?

Thanks much.

***

If the user is not calling the sub from the cell but is initiating the sub
and wants to refer to the current cell, then they can use ActiveCell or
Selection to specify that cell.

" wrote:

This works fine in all Functions I've written but throws an 'Object
required' in a Sub I'm writing:

Dim R as Range
Set R = Application.Caller

If Application.Caller is invalid in a Sub, then what expression DO you
use in a Sub to return the cell the user was on when he launched the
Sub?

Thanks much.

***

Thanks to ALL who answered.

I may not've stated my Q clearly enough. I forebore from providing any
context in an attempt to be simple. Which was prob. a bad idea.

The user launches my Sub by macro keystroke (Shift-Ctrl-<x).

After killing ScreenUpdate, the Sub walks thru various sheets 'n'
cells in the workbook and does stuff. My Q is:

On exit, how do I return to the user's cell, so when I reenable the
screen, he has no clue I was traveling all around his WB?

I do have a working solution: On Sub launch (in Groton?), I push
ActiveCell into a var, and before exit I pop it with
OrigCell.Sheet.Activate followed by OrigCell.Activate.

But to me that smells like a kluge. I don't have to do that in a
Function, do I? Application.Caller retains its value thruout a
Function's life, doesn't it? Ergo, what's a Sub's equivalent?

It seems to me Excel oughta know the user's home cell thruout a
routine's life. This pervert, at any rate, doesn't care whether it's a
Function, Sub, or event handler (in which case you're handed Target on
a silver platter anyway, right?).

But on further thought, well, I guess I can surmise why the answer
might be no. Because Subs can do things Functions can't, right? Like
do interactive things, Goto places, etc. So yeah, I comprehend and
readily accept such differences.

So it's a yes-no question. I apologize if I caused folks to answer as
if otherwise.

And again, we are all indebted to you Knights of the Net.

***

First, it's very rare to have to use any select's/activate's in your code.
Instead of:

worksheets("Sheet2").select
range("a1").select
selection.value = "hi"

You could use:
worksheets("Sheet2").range("A1").value = "hi"

If you rewrite your code, you may find that it's easier to maintain and debug.

But you could save the current location before you do anything:

Option Explicit
Sub YourSubNameHere()

Dim ActCell as range
dim CurSel as range

set cursel = selection
set actcell = activecell
...
do a whole bunch of stuff.
...
application.goto cursel
actcell.activate

End Sub


wrote:

Thanks to ALL who answered.

I may not've stated my Q clearly enough. I forebore from providing any
context in an attempt to be simple. Which was prob. a bad idea.

The user launches my Sub by macro keystroke (Shift-Ctrl-<x).

After killing ScreenUpdate, the Sub walks thru various sheets 'n'
cells in the workbook and does stuff. My Q is:

On exit, how do I return to the user's cell, so when I reenable the
screen, he has no clue I was traveling all around his WB?

I do have a working solution: On Sub launch (in Groton?), I push
ActiveCell into a var, and before exit I pop it with
OrigCell.Sheet.Activate followed by OrigCell.Activate.

But to me that smells like a kluge. I don't have to do that in a
Function, do I? Application.Caller retains its value thruout a
Function's life, doesn't it? Ergo, what's a Sub's equivalent?

It seems to me Excel oughta know the user's home cell thruout a
routine's life. This pervert, at any rate, doesn't care whether it's a
Function, Sub, or event handler (in which case you're handed Target on
a silver platter anyway, right?).

But on further thought, well, I guess I can surmise why the answer
might be no. Because Subs can do things Functions can't, right? Like
do interactive things, Goto places, etc. So yeah, I comprehend and
readily accept such differences.

So it's a yes-no question. I apologize if I caused folks to answer as
if otherwise.

And again, we are all indebted to you Knights of the Net.

***

--

Dave Peterson

Dave:

From what you say, sounds like I just gotta continue what I'm doing,
pushing then popping the cell the user was on, before Goto-ing
someplace in the Sub.

Once again thanks very much for your reply.

***

No, I'm not recommending that.

I'd recommend that you look at your code and re-write it so that there are no
..select's and .activate's.


wrote:

Dave:

From what you say, sounds like I just gotta continue what I'm doing,
pushing then popping the cell the user was on, before Goto-ing
someplace in the Sub.

Once again thanks very much for your reply.

***

--

Dave Peterson