Stay on same row when selecting next sheet

Trowa - Oct 20, 2009 at 08:49 AM
 Trowa - Nov 26, 2009 at 08:05 AM

I would like to know how to stay on the same row when selecting another sheet.

My file contains 17 sheets. Sheets 2 and 5 till 16 contain names and will be refered to as target sheets.
When I select one of these names on one of the target sheets and select another target sheet, I would like the same name on the same row selected.

I input some data on row 17 of sheet 2. On sheet 5 I input some more data on the same row. So I would like row 17 to be selected when I select sheet 5.
When I input some data on row 187 of sheet 2 and go back to sheet 5, the active row will still be 17. It would speed up the process if row 187 would already be selected.

I hope I made myself clear.

Best regards,

3 responses

I have got something like this,

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

r = ActiveCell.Row
Cells(r, 3).Select

End Sub

but the code won't recognise the active row.
How do I exclude certain sheets?
What to do?

aquarelle Posts 7135 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2023 491
Nov 23, 2009 at 03:43 PM
Hello Trowa,

I don't know if you found a solution to this question but if it is not the case, try this proposition. You have to place it in "ThisWorkbook". Normally works if you select a cell, a range of cells, a row or any sort of selections.

Option Explicit

Dim LastTarget As String
Dim FirstVisibleCell As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
LastTarget = Target.Address
FirstVisibleCell = "R" & ActiveWindow.VisibleRange.Row & "C" & ActiveWindow.VisibleRange.Column
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Application.EnableEvents = False
Application.Goto Reference:=FirstVisibleCell, Scroll:=True
Application.EnableEvents = True
End Sub

Best regards

Hi Aquarelle,

Actually I had already given up hope to solve this query, but your reply has filled me with hope ones again.

The code works when I implement it in an empty workbook, however I need to add the code to a workbook where there already is some code in ThisWorkbook.
It's the same code you provided me with a few month ago. The code let's me change more then 3 colors according to a cell's value.
The code is as followed:

(Code can be found in the uploaded file at the bottom of the message.)

What's the best way to implement the code to make both codes work?
I tried several things, but keep getting errors.

About the code for staying on the same row when selecting another sheet:
The workbook contains 5 sheets I would like to exclude from the code, since they don't contain a list of names.
These 5 sheets have the following names: Handleiding, Jaaroverzichten, Overzichtsgrafieken 1, Overzichtsgrafieken 2 (This graphic is used for leap years and is hidden), Afwijkende werkroosters.

By typing the last piece of this message I realize it's best to upload my file. Since the file is already in use I removed passwords and replaced last names by a letter of the alphabet.
Here's the file:

Kind regards,
aquarelle Posts 7135 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2023 491
Nov 24, 2009 at 04:30 PM
Hi Trowa,

Look at this new file and tell me if it's OK :

Best regards and see you soon
You did it again Aquarelle, given me exactly what I wanted.

Many thanks,