Go to same cell on any sheet

shy9999

New Member
Joined
Jan 16, 2008
Messages
9
Hi

I have sheets named mon,tues,wed etc & I when I click on any of the sheets, I would like to go to the same cell on the sheet activated as the sheet I just left.

So, if I'm on A7 on mon sheet, when i click fri sheet, a7 be the active cell on that sheet.
Or, if on thurs sheet on B42 & click on tues, again be on the previous address, B42, on the tues sheet.

I've tried with Worksheet_Activate & Deactivate & storing the previous address globally from Worksheet_SelectionChange & then going to that range, but with no success.

I also looked at Worksheet_Activate, but again I had no luck as I don't know how to store the previous activecell, or pass that info on & then select it on the new sheet.

I thought it would be quite easy (& it may well be), but it isn't easy enough for me !:confused:!

thanks for any pointers
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
See if this works for you.

Create a standard module with the following code:
Code:
Public Active As String

Then in each of your sheets, (Mon, Tues, etc) place the following in the sheet module:

Code:
Private Sub Worksheet_Activate()
Range(Active).Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Active = ActiveCell.Address
End Sub

EDIT: If you experience some screen flicker when selecting a new sheet, set ScreenUpdating to False at the start of the Worksheet_Activate event, and back to true at the end of the event.
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I think you need to use a combination of several events..

put this in the ThisWorkbook Module...

Code:
Public MyRange As String
 
Private Sub Workbook_Open()
MyRange = ActiveCell.Address
End Sub
 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range(MyRange).Select
End Sub
 
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MyRange = Target.Address
End Sub

Hope this helps..
 
Upvote 0

shy9999

New Member
Joined
Jan 16, 2008
Messages
9
Thank you so much for your solutions, but I forgot to mention that if necessary I need to scroll there too...

I've used
Application.Goto Range(MyRange), True
which works, but it positions the cell in the top left, which is ok, but it would be perfect if I get the cell in the same screen position as it was previously. But this is probably getting silly....

But many thanks again, it works!
 
Upvote 0

Forum statistics

Threads
1,190,836
Messages
5,983,174
Members
439,825
Latest member
AdamS92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top