How to Find ActiveCell Address on different sheet

[zit1343]

Hi
is there way to find out which cell is active on another sheet,
like if I am sheet2, I want to know which cell is active on sheet1.

I will use to write VBA code.

If in Sheet1 Cell F4 is active than in Sheet2 A2 range will be IV4
if in Sheet1 Cell F5 is active than in Sheet2 A2 range will be IV5
and so on

please please please help me do this

[shg]

Please Login or Register  to view this content.

E.g., in the Immediate window, with Sheet1 active,

Please Login or Register  to view this content.

[MarvinP]

Hi zit1343,

Three Pleases is a lot of begging. Here is some code that may be what you want. I turn off screen updatting, switch to Sheet1 and grab where the active cell is, then flip back to where I was. See if you can modify this code to get what you need.

Please Login or Register  to view this content.

It looks like shg and I did similar things. It looks like you have to flip to that window to get where the cell is.

[zit1343]

Hey
thanks for help, I been trying to figure this out for many hours
can you please explain me how to use it
I am not understanding it
thanks

[MarvinP]

Hi,

There is no way to directly, from sheet2, tell where the cursor will be when you tab back to Sheet1. What we do is use VBA code and flip back to Sheet1 without you seeing it. That is the ScreenUpdating part. Then we report back where the cursor is using the Row and Column of the ActiveCell. Then we flip back to where you were.

Give us a sample of how you are needing or using the information and we can help more.

[zit1343]

Please see at attached file
Sheet1 is the menu where user selects the item they want to pick
if user select A041
it will take you to sheet2 Cell A1,
but they will still need to maunally select A041_Tag_Id from the drop down menu from sheet2

[zit1343]

Please Login or Register  to view this content.

what do you mean by this code
I don't understand it

[shg]

The VBE includes a window called Immediate; do Ctrl+G if it's not visible.

Copy what I posted into the Immediate window and press Enter.

[zit1343]

here do I insert this code

Function ActiveCellOnSheet(wks As Worksheet) As Range
Dim wksOld As Worksheet

Set wksOld = ActiveSheet
Application.ScreenUpdating = False
wks.Select
Set ActiveCellOnSheet = ActiveCell
wksOld.Select
Application.ScreenUpdating = True
End Function

[shg]

In a standard code module.

[Whizbang]

I wonder why there is not a way to do this without selecting the sheet. Obviously the active cell is stored somewhere within the file. Otherwise every time you navigate away from then back to the sheet (or close and re-open the file) the active cell would be A1 or something. Why do you suppose there isn't an easy/obvious way to retrieve this stored value?

[zit1343]

well after selecting the cell the data comes from another server, & I have over 10,000 items in those servers, and it can go up to 100,000 data for 1 single Cell eg A051, I wanna be able to dectect which one is clicked, if A051 is click than in Sheet2 wirte the tag name which belong to A051