Run-Time error 1004

[Foynxter1]

Hi,
I am new to VBA and am experiencing following problem.
I have created excel spreadsheet and need to record date record created, who created the record and who modifies the record as per below code

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Additional Charges").Select
If Target.Column = 1 And Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1) = Format(Now(), "dd/MM/yy")
'Set the user who created the record
Cells(Target.Row, 4).Value = UserName()
Else
End If
'Set the user who modified the record
Cells(Target.Row, 5).Value = UserName()
End Sub

This works well but when I need to delete a row/record using the right click menu on the row heading I get the error "Run-Time error 1004" - Application-defined or object-defined error.
I have driven myself almost insane trying to resolve but with no luck.
Any help greatly appreciated.
Thanks

 

[Norie]

Which sheet is this code for?

 

[Foynxter1]

Hi - the sheet is called "Additional Charges"
Thanks

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub

    If Target.Offset(0, 1).Value = "" Then
        Target.Offset(0, 1) = Format(Now(), "dd/MM/yy")
        'Set the user who created the record
        Cells(Target.Row, 4).Value = UserName()

    End If
    
    'Set the user who modified the record
    Cells(Target.Row, 5).Value = UserName()
    
End Sub

 

[Ken Cowen]

The OP probably would rather have

If Target.cells.count> 1 then exit sub

as the first line as it appears he wants the code to run for changes outside the first column (logs the user in column 5)

UserName doesn't work for me? Is that a custom function? I usually have to use Application.username.

Anyway, if the OP wants the code triggered by any worksheet change, and wants to alloy the use of the right-click functionality to delete, there needs to be a way to bail out of the procedure and you came up with a good one.

 

[Foynxter1]

Norie
You are a genius - works perfectly. Easy when you know how
Thanks your help - I'm sure there will be many more questions as I go along. Thanks again

 

[mole999]

1004 usually implies the sheet is not there (deleted, hidden, misspelt etc)

 

[Miczv]

I am getting the Run-time error '1004' only when I protect the sheet, when the sheet is not protected the VBA works perfectly.
What do I need to do to get it to work when the sheet is protected? Is it something I need to do in the VBA or is it in the Protect sheet settings?

[TABLE="width: 241"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Sub Order()[/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] If Range("A6").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("6").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A7").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("7").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A8").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("8").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A9").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("9").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A10").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("10").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A11").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("11").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A12").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("12").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A13").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("13").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A14").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("14").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A15").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("15").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A16").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("16").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A17").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("17").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A18").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("18").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A19").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("19").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A20").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("20").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Range("A21").Value = 0 Then[/TD]
[/TR]
[TR]
[TD] Rows("21").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]