Runtime error 438

[MsAgentM]

The code below was working fine yesterday but now I get a runtime error 438 and then the entire thing stops working. Any idea what it going on?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "1107"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Unprotect
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Sheets("1107").Target.Value <-- Error happens here.
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Now



Sheets("LogDetails").Columns("A").AutoFit
Application.EnableEvents = True
End If
End Sub

[rlv]

Target is a range being passed as parameter to the Sub, but you are trying to use it like a worksheet property

        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Sheets("1107").Target.Value ' <-- Error happens here.

Will just using Target work for you?

        Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value

[Paul_Hossler]

1. ActiveSheet might not always what you want, maybe

If sh.Name <> "LogDetails" Then

2. Where does sheet '1107' come into play?

3. The first Offset goes to the row below the last used one, Column A, but the next 3 refer to the last used row ( '0'), columns B, C, and D


What are you trying to do?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim sSheetName As String
sSheetName = "1107"

If ActiveSheet.Name <> "LogDetails" Then

    Application.EnableEvents = False
    Sheets("LogDetails").Unprotect
    Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Address(0, 0)
    Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Sheets("1107").Target.Value <-- Error happens here.
    Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Environ("username")
    Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Now

    Sheets("LogDetails").Columns("A:D").AutoFit
    Application.EnableEvents = True

End If
End Sub

[MsAgentM]

That works! thanks so much

[MsAgentM]

Hi Paul_Hossler,

Sheet”1107” is where the information is coming from and thesheet that is being monitored for changes. The code as a whole is simplylogging any changes on the 1107 worksheet onto the LogDetails worksheet. Whenplaying with track changes, it needed to be turned on by the user everytime andI don’t think users will bother with it, so I have opted to code in a worksheetfor a sort of track changes. The first line that looks for the next empty rowis followed by code that looks for the next empty cell in that row, which iswhy the offset only offsets the column and not the row there.

[Paul_Hossler]

I think it'd be simpler to put this into the 1107 code module since it seems that only the 1107 sheet needs changes logged

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    With Worksheets("LogDetails")
        .Unprotect
        
        With .Cells(1, .Rows.Count).End(xlUp)
            .Offset(1, 0).Value = Target.Address(0, 0)
            .Offset(1, 1).Value = Target.Value
            .Offset(1, 2).Value = Environ("username")
            .Offset(1, 3).Value = Now
        End With
    
        .Columns("A:D").AutoFit
        
        .Protect
    End With
    
    Application.EnableEvents = True
End Sub

Capture.JPG

[Haaris]

Hi guys, i'm new in this form and vba. I'm coding an excel workbook with multiple sheets. trying to set password and user name for each worksheet in order to keep confidentiality and staff accessing other peoples sheet. I coded the form and now, i'm getting a runtime error "438". I've added the screenshot and the location where the error is.

Screenshot 2018-04-30 11.26.35.jpg

thank you all in advance, and looking forward to your soonest response.