How to get the activecell of inactive worksheet ?

S

steven

If the active worksheet is sheet1, is it possible to get the activecell value in sheet2 without selecting it ?

Thank you
 
N

Nigel

Worksheets("Sheet2").Cells(1,1).Value

or

Worksheets("Sheet2").Range("A1").Value

The first is useful because it is easier to program the row and column
refrence in the Cell(row,col), the second is ideal if you are just picking a
specfic value only.

HTH
Cheers
Nigel

steven said:
If the active worksheet is sheet1, is it possible to get the activecell
Click to expand...
value in sheet2 without selecting it ?
 
S

steven

Thx for your reply. How about if I don't know the exact refrence in sheet2 ?
I need to get the activecell reference in sheet2 but don't want to select sheet2 first.
 
N

Nigel

AFAIK - The active cell only relates to the worksheet in the active window,
the selection relates to a specified range. So I do not see any other way
of selecting from what would be the active cell until you activate the
sheet. If you do not want the user to see the switch to sheet2 then turn
off the Application.ScreenUpdating then activate sheet2 pick up the Active
Cell value and activate sheet1. Turn Application.ScreenUpdating back on.

something like....

Application.ScreenUpdating = False
Sheets("Sheet2").Activate
N = ActiveCell
Sheets("Sheet1").Activate
Application.ScreenUpdating = True
' process using the value in N

Cheers
Nigel
 
B

Bob Phillips

Steven,

Activecell applies to the active window, so there is only one. You can get
the selection from any window though. Here is a post from Dave Peterson a
couple of days ago on this topic

-----------------------------------------------------------------------

If you look at VBA's help for Selection, you'll see that it applies to the
application or window object.

You'll have to go through one of them to get it.

MsgBox Windows(1).Selection.Address & "--" & Windows(1).Caption
MsgBox Windows(2).Selection.Address & "--" & Windows(2).Caption

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

steven said:
If the active worksheet is sheet1, is it possible to get the activecell
Click to expand...
value in sheet2 without selecting it ?
 
D

Dave Peterson

I think Activecell also belongs to the window object. So if you have multiple
windows available (I usually don't!), you could get it that way:

Dim myWindow As Window
For Each myWindow In ActiveWorkbook.Windows
MsgBox myWindow.Caption & "-" & ActiveCell.Address(external:=True)
Next myWindow

But I wouldn't use this. I'd just go and look like Nigel did.
 
Top