Worksheet_Deactivate error code 1004

[Ironman]

Hi

I wrote the below code so the sheet opens at the bottom row.

Private Sub Worksheet_Deactivate()
Range("A23358").End(xlUp).Offset(-15, 0).Select
End Sub

I didn't write this as a Worksheet_Activate event because if I did that then the links I've created from other sheets to other rows in this sheet won't run.

I don't know why I get Error 1004 though.

Help appreciated.

Many thanks!

 

[Joe4]

If you end up at a row before row 16, moving up 15 rows will not be possible.
Maybe try amending it like this:

Private Sub Worksheet_Deactivate()
    Dim lr as Long
    lr = Range("A23358").End(xlUp).Row
    If lr > 15 Then
        Range("A" & lr -15).Select
    Else
        Range("A1").Select
    End If
End Sub

 

[Ironman]

Hi Joe, many thanks for replying. I got the same 1004 error I'm afraid with this row

Range("A" & lr - 15).Select

Would you like to see the rest of the sheet code?

 

[Joe4]

Would you like to see the rest of the sheet code?

Sure.
Do you also have any protected, merged, or hidden cells in column A?

Also, let me know what the message box returns before you get the error in this code:

Private Sub Worksheet_Deactivate()
    Dim lr as Long
    lr = Range("A23358").End(xlUp).Row
    MsgBox("Last row is: " & lr)
    If lr > 15 Then
        Range("A" & lr -15).Select
    Else
        Range("A1").Select
    End If
End Sub

 

[Ironman]

Here it is Joe.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = Range("A1").Address Then
Range("A23358").End(xlUp).Offset(-15, 0).Select  'goes to route list heading.  If you want it to go to the instructions row at the bottom, amend to (0,0)
End If

If Target.Address = Range("B1").Address Then
Range("A2").Select
End If

If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim findWhat$, FindWhere As Variant
findWhat = Left(Target.Value, 255)

Set FindWhere = _
Sheets("Training Log").Columns(9).Find(What:=findWhat, LookIn:=xlFormulas, Lookat:=xlPart, MatchCase:=True)
If FindWhere Is Nothing Then Exit Sub

Dim iIndex As Long
iIndex = Sheets("Training Log").Cells(FindWhere.Row, 8).DisplayFormat.Interior.Color
Target.Hyperlinks.Add _
Anchor:=Target, _
Address:="", _
SubAddress:="'Training Log'!I" & FindWhere.Row, _
TextToDisplay:="LOG ENTRY", _
ScreenTip:="Go to Training Log I" & FindWhere.Row 'DON'T EVER RENAME THE ABOVE LINE "COMMENTS" or the hyperlink will locate the one cell in Training Log that contains the word "Comments" H1869 - see above comments 07.03.2019
Target.Interior.Color = iIndex

With Target
.Font.Name = "Comic Sans MS"
.Font.Size = 7
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
.HorizontalAlignment = xlCenter
Application.ScreenUpdating = True
End With
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' Courtesy of Joe4 (adapted) https://www.mrexcel.com/board/threads/can-a-message-box-title-be-coded.1185875/#post-5777683
' Message box every 50 times Route 33 is run and on every 49th run to notify in advance
Dim a As Integer
Dim title As String
a = Range("AnalysisRoute33Total").Value Mod 50 'Ling Bob/Bents Ln route

If 50 - a <= 1 Then MsgBox "The next time you run this route will be your " & Range("AnalysisRoute33Total").Value + 50 - a & "th!", vbInformation, "Route 33"
If a = 0 Then MsgBox "You have now run this route " & Range("AnalysisRoute33Total").Value - a & " times!", vbInformation, "Route 33"
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   
On Error GoTo Whoa
  
    Application.EnableEvents = False
  
    If Not Intersect(Target, Range("AnalysisRoutesList")) Is Nothing Or _
    Not Intersect(Target, Columns(2)) Is Nothing Then
        findRange Range("B" & Target.Row)
        Cancel = True
    End If
      
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Function findRange(rngRange As Range) As Range
    Dim searchStr As String
    searchStr = Replace(rngRange.Value, ".", "")
   
    Dim foundRange As Range
    Set foundRange = Columns(6).Find(What:=searchStr, _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)
   
    If Not foundRange Is Nothing Then
        foundRange.Select
    Else
        'MsgBox "Invalid search - double click ROUTE # cell only", vbCritical, "Invalid Cell Selection" 'shknbk2's old code
        findRange Range("F" & rngRange.Row)
    End If
End Function

Private Sub Worksheet_Deactivate()
    Dim lr As Long
    lr = Range("A23358").End(xlUp).Row
    MsgBox ("Last row is: " & lr)
    If lr > 15 Then
        Range("A" & lr - 15).Select
    Else
        Range("A1").Select
    End If
End Sub

Last row in message box is 655. All the other code works perfectly.

 

[Joe4]

OK, not that your "Worksheet_Deactivate" script is selecting a cell, but every time you select a cell, it calls the "Worksheet_SelectionChange" script, which also appears to be selecting cells. So I think you may be getting caught in a loop. You should temporarily disable events, so your "Worksheet_Deactivate" script does not kick off the "Worksheet_SelectionChange" script.

You would do that like this:

Private Sub Worksheet_Deactivate()
    Dim lr as Long
    lr = Range("A23358").End(xlUp).Row
    Application.EnableEvents = False
    If lr > 15 Then
        Range("A" & lr -15).Select
    Else
        Range("A1").Select
    End If
    Application.EnableEvents = True
End Sub

 

[Ironman]

Thanks Joe, but unfortunately it's still Error 1004

Range("A" & lr - 15).Select

 

[Ironman]

Sorry, I forgot to answer your question - there are no protected, merged, or hidden cells in column A.

 

[JoeMo]

I don't use the worksheet_deactivate event code much, but it seems to me that deactivating by activating another tab makes the other tab the activesheet. So, it's no longer possible to selecet any cell on the dectivated sheet the code is written for. You will always get a 1004 error on attempting to do that. Run the simple code below as is and it runs fine. Then uncomment the cell selection line and run it again.

Private Sub Worksheet_Deactivate()
MsgBox Me.Name & " has been DEACTIVATED"
Range("A1").Value = "DEACTIVATED"
'Range("A1").Select
End Sub

 

[Ironman]

@JoeMo yes, your logic makes sense. It's no big deal, I just thought it might be possible to do it, although I must admit I've always struggled understanding the worksheet_deactivate event.
Many thanks for your help anyway Joe4 and JoeMo