Cell selection altered after Worksheet Activate code ends

[John W]

I have four worksheets for storing diferent types of data, all four sheets have identical code attached.
The sheets are protected and maybe filtered.
The code is intended to stop the user selecting anywhere except column 1 on any row after row 3. Column 1 can be used for selecting that row as input to a data entry form.
Only one of the sheets does not work correctly, The selection is always moved to row 4 column 1 when the activate code has ended.
I have single stepped through the VBA code.

Tax data.xlsm
	A	B	C	D	E	F	G	H	I	J		L
1	06/04/2020 Mon	141	6,330.00	190.0						42
2	06/04/2019 Sat	177	6,320.00	415.5						535
3	Date	Name	Fee	Hours	Start	Venue	Finish	Distance1	Distance2	Journeys		Notes
4	07/03/2016 Mon	Accounts	#	3.0						0
5	24/03/2016 Thu	Accounts	#	3.0						0
6	06/04/2016 Wed	Accounts	#	8.0						0
7	06/04/2016 Wed	Sophie Timms	40.00	1.5	TN33 0LH	TN33 0DE	TN33 0LH	3	3	2
8	12/04/2016 Tue	Alice Gildea	40.00	1.0	TN33 0LH	TN33 0DE	TN33 0LH	3	3	2
9	15/04/2016 Fri	Kenneth Fox	35.00	1.0						0
10	16/04/2016 Sat	Alison Norris	35.00	1.0						0
11	16/04/2016 Sat	Caroline Taylor	60.00	1.5	TN33 0LH	TN34 1BA	TN33 0LH	9	9	2
12	16/04/2016 Sat	Kristal Seidler	40.00	1.0						0
13	16/04/2016 Sat	Practice	#	2.0						0
14	23/04/2016 Sat	Bethany Williams	35.00	1.0						0
15	23/04/2016 Sat	Kenneth Fox	35.00	1.0						0
16	23/04/2016 Sat	Practice	#	2.0						0
Student

Cell Formulas
Range	Formula
A1	A1	=IF(TODAY() > DATE(YEAR(TODAY()),4,5),DATE(YEAR(TODAY()),4,6),DATE(YEAR(TODAY())-1,4,6))
B1	B1	=COUNTIFS(Student!$A4:$A10000, ">=" & $A1,Student!$A4:$A10000, "<=" & DATE(YEAR($A1)+1,4,5),Student!$C4:$C10000,"<>#")
C1:D1	C1	=SUMIFS(Student!C3:Student!C10000,Student!$A3:Student!$A10000, ">=" &$A1,Student!$A3:Student!$A10000, "<=" &DATE(YEAR($A1)+1,4,5))
A2	A2	=DATE(YEAR(A1)-1,4,6)
B2	B2	=COUNTIFS(Student!$A4:$A10000, ">=" & $A2,Student!$A4:$A10000, "<=" & DATE(YEAR($A2)+1,4,5),Student!$C4:$C10000,"<>#")
C2:D2	C2	=SUMIFS(Student!C3:Student!C10000,Student!$A3:Student!$A10000, ">=" &$A2,Student!$A3:Student!$A10000, "<=" &DATE(YEAR($A2)+1,4,5))
J1	J1	=SUMPRODUCT(--(A4:A10000>=A1),--(A4:A10000<DATE(YEAR(A1)+1,4,6)),(H4:H10000*J4:J10000))+SUMPRODUCT(--(A4:A10000>=A1),--(A4:A10000<DATE(YEAR(A1)+1,4,6)),(I4:I10000*J4:J10000))
J2	J2	=SUMPRODUCT(--(A4:A10000>=A2),--(A4:A10000<DATE(YEAR(A2)+1,4,6)),(H4:H10000*J4:J10000))+SUMPRODUCT(--(A4:A10000>=A2),--(A4:A10000<DATE(YEAR(A2)+1,4,6)),(I4:I10000*J4:J10000))

The worksheet code is as follows

Private Sub Worksheet_Activate()
   If Sheet_Rebuild = True Then Exit Sub
   Setup_OnKey
   Select_Column1
End Sub

Private Sub Worksheet_Deactivate()
   If Sheet_Rebuild = True Then Exit Sub
   Clear_OnKey
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Sheet_Rebuild = True Then Exit Sub
   If ActiveSheet.ProtectContents = False Then Exit Sub
   If First_Pass = False Then
       First_Pass = True
   Else
       Worksheet_Selection
   End If
End Sub

Sub Select_Column1()
    rw = ActiveCell.Row
    cl = ActiveCell.Column
    If rw >= FirstRow And cl > 1 Then
        cl = 1
        Cells(rw, cl).Select
    End If
End Sub


Routines called are:
Sub Sim_Key_Tab()
    Simulate_Keys ("tab")
End Sub
Sub Sim_Key_down()
    Simulate_Keys ("down")
End Sub
Sub Sim_Key_up()
    Simulate_Keys ("up")
End Sub
Sub Sim_Key_left()
    Simulate_Keys ("left")
End Sub
Sub Sim_Key_right()
    Simulate_Keys ("right")
End Sub

Sub Setup_OnKey()

    Application.OnKey Key:="{TAB}", Procedure:="Sim_Key_Tab"
    Application.OnKey Key:="{down}", Procedure:="Sim_Key_down"
    Application.OnKey Key:="{UP}", Procedure:="Sim_Key_up"
    Application.OnKey Key:="{LEFT}", Procedure:="Sim_Key_left"
    Application.OnKey Key:="{Right}", Procedure:="Sim_Key_right"
   
End Sub

Sub Clear_OnKey()

    Application.OnKey Key:="{tab}"      ', Procedure:=""
    Application.OnKey Key:="{down}"     ', Procedure:=""
    Application.OnKey Key:="{up}"       ', Procedure:=""
    Application.OnKey Key:="{left}"     ', Procedure:=""
    Application.OnKey Key:="{right}"    ', Procedure:=""
   
End Sub

Sub Simulate_Keys(KeyName As String)
    If Trace_Sw Then Debug_Print ("Simulate_Keys~" & KeyName)

    wn = ActiveSheet.Name
    rw = ActiveCell.Row
    cl = ActiveCell.Column
   
    If Worksheets(wn).ProtectContents = True Then
        cl = 1
        Select Case LCase(KeyName)
            Case "tab", "down", "right"
                rw = rw + 1
                If rw < 3 Then rw = 3
            Case "up", "left"
                rw = rw - 1
                If rw < 3 Then rw = 3
        End Select
    Else
        Select Case LCase(KeyName)
            Case "tab"
                cl = cl + 1
            Case "down"
                rw = rw + 1
            Case "right"
                cl = cl + 1
            Case "up"
                rw = rw - 1
                If rw < 1 Then rw = 1
            Case "left"
                cl = cl - 1
                If cl < 1 Then cl = 1
        End Select
    End If
    Worksheets(wn).Cells(rw, cl).Select
   
End Sub

 

[steve the fish]

If you are using sheet protection just change the cells you want them to access as unlocked then when protecting dont allow selection of locked cells? Could that work??

 

[John W]

That was my first idea, but unfortunately that allows the user to change the data, I only want them to be able to select the row, not to change it.

 

[John W]

On further investigation I have found the cause. Code which I had previously added at the workbook level was causing the problem but was only doing it for one sheet as It was incorrcetly identifying the other 3 sheets, but what I still don't understand is why the execution of the code was not visible as I was stepping through the code.