Same active row when changing worksheets

Brechtje

New Member
Joined
Jun 8, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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:
    VBA Code:
    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):
Excel solution 1.png


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.

1591604791343.png


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
VBA Code:
Sh.Range(ActiveCellAddress).Activate
Could be I am not adding the code at the correct place? I added
VBA Code:
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 ;)
 

Attachments

  • Excel conditional formatting.png
    Excel conditional formatting.png
    12.2 KB · Views: 1

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,493
Office Version
  1. 2010
Platform
  1. Windows
try putting this into the workbook code:
VBA 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

New Member
Joined
Jun 8, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
try putting this into the workbook code:
VBA 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:


VBA Code:
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

New Member
Joined
Jun 8, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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...)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,446
Messages
5,547,971
Members
410,820
Latest member
Prepost
Top