Stay on same row when selecting next sheet

Solved/Closed
Trowa - Oct 20, 2009 at 08:49 AM
 Trowa - Nov 26, 2009 at 08:05 AM
Hello,

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.

Example:
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,
Trowa

3 replies

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?

Regards,
Trowa
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
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
Range(LastTarget).Select
Application.EnableEvents = True
End Sub


Best regards

0
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:
https://authentification.site/files/19452605/Copy_of_Verlofoverzicht_2009.xls

Kind regards,
Trowa
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Nov 24, 2009 at 04:30 PM
Hi Trowa,

Look at this new file and tell me if it's OK : https://authentification.site/files/19464092/Copy_of_Verlofoverzicht_2009_2.xls

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

Many thanks,
Trowa
0