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



22021

[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.

22133

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