Same active row when changing worksheets

[Brechtje]

Hello all,

I am new to this forum and pretty new to VBA in general. I have created a file for work and I am very happy with it, but I would like to do one last thing and I do not know how to do it. My apologies for the long post, but I tried to make it as clear as possible what my question is and what I have tried so far.

A little background:
It is a file to keep track of our scientific studies (logistics, inclusions, correspondence, etc). To keep it manageable I created different tables, each on its own sheet, for different kinds of data. Each sheets has a few columns of identifying data, to easily identify the study; these data are thus repeated on each sheet (I referenced the columns from the first sheet with formulaR1C1, so the data is automatically updated on the other sheets when it is altered on the first sheet). So all the studies are in the same rows on all worksheets and sorting is disabled. I have protected the worksheets but enabled formatting and selecting cells/rows/columns (with UserInterFaceOnly).

I have added code to highlight the currently active row with conditional formatting. I used a tutorial (in Dutch) to achieve this and it works fine, like this:

1. I selected an empty cell in my worksheet and defined a name, using the 'Formula' tab (my defined name is GesRij)
2. I used the following code on the worksheet:
   

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   [GesRij] = Target.Row
   End Sub

3. Then, to create a red border around the active row, I used the following formula for the conditional formatting (not VBA) (I use the Dutch Excel version):
   
   =RIJ(A7)=GesRij
   
   and applied this on my entire table

So far, so good. Here is my actual issue:
To make the file as foolproof as possible (i.e. to minimize the chance of a user editing data for the wrong study), I would like to add code that selects the same row on a different worksheet. So if I am editing study X in row 5 on sheet 1, for example, and I change worksheets to edit more data on study X, I would like to immediately have row 5 as my active cell/row on the new sheet. This way, I won't accidentally edit data for study Z since row 7 was selected on that sheet from a previous action. Ideally, this would work from every sheet to every sheet. For example, on sheet 1 I work in row 5 and change to sheet 2 to edit more data in row 5, but when I then select row 12 on that sheet I would like to stay in row 12 when changing to another sheet. Hopefully that makes sense!

I have searched extensively but I cannot find the answer, or perhaps I have found the answer but I have not understood the code since I am relatively new to VBA. I found the following solution from Stack overflow, but as mentioned in the post this is a one-way example and I do not know how to edit it to make it work both ways. I also had trouble experimenting with this since I already have another Worksheet_SelectionChange (see above, for the conditional formatting):

Subsequently, I tried the solution in the screenshot below from the same thread, hoping this would work-around the issue of multiple Worksheet_SelectionChange events.

However, I cannot get this to work: When changing to another sheet I get runtime error 1004: application-defined or object-defined error occurs. The error occurs on line

Sh.Range(ActiveCellAddress).Activate

Could be I am not adding the code at the correct place? I added

Dim ActiveCellAddress As String

to an empty Module 2, and the workbook events in the code for 'ThisWorkbook'.

Any help on how to achieve this (if it is even possible) would be greatly appreciated! And if my question is not clear enough, I will gladly clarify.
Again, my apologies for this long post! Awesome if you stayed with me to the end

 

[offthelip]

try putting this into the workbook code:

Public rowno As Long


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range(Cells(rowno, 1), Cells(rowno, 1)).Select

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
rowno = Target.Row

End Sub

 

[Brechtje]

try putting this into the workbook code:

Public rowno As Long


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range(Cells(rowno, 1), Cells(rowno, 1)).Select

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
rowno = Target.Row

End Sub

Thank you for the suggestion. Unfortunately, I get the same runtime error, on the line 'Range(Cells(rowno, 1), Cells(rowno, 1)).Select.'
Could it be because I already have a Workbook_Open() code, in which I protect the worksheets? I added this to ensure that the workbook always starts off being protected, in case someone unprotected it manually and forgot to re-protect it. See below:

Private Sub Workbook_Open()

'Create loop to protect all worksheets upon opening file

Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
    sht.Protect , UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows:=False, AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    
Next sht

End Sub

 

[Brechtje]

Nevermind, I solved it and now it works perfectly! I am so happy!
I figured out I had to place the 'Public rowno As Long' al the way at the top, before the Workbook_Open() event.
Thank you very much, you made my day (and saved me from hours of trying further after already trying for 1,5 hours as a newbie...)