Macro issue by inserting or deleting a row!

[Jurajko]

Hi everyone,
i need a help with the VBA code, that should insert the current date and username as soon as the word "done" is inserted in some of the cells in the column S.
It works until I delete or insert a new row. Then I get the Issue ´13´ and the line with If Target.Value ="done" is highlighted.
Can you help me to adjust the code, so it works without any Issue? Thank you in advance!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("S:S")) Is Nothing Then
If Target.Value = "Done" Then
Target.Offset(0, 7).Value = Date
Target.Offset(0, 8).Value = Environ("username")
Else
Target.Offset(0, 7).ClearContents
Target.Offset(0, 8).ClearContents
End If
End If
End Sub

 

[vcoolio]

Hello Jurajko,

I don't see a problem with your code. However, is it "Done" or is it "done"?
In this situation, your code is case sensitive.
If you've specified the target value as "Done", then that is how it needs to be typed into the target cell, not as "done".

You could overcome it being case sensitive as follows:-

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("S:S")) Is Nothing Then Exit Sub
    
    If Target.Value = "Done" Then
        Target.Offset(, 7).Value = Date
        Target.Offset(, 8).Value = Environ("username")
    Else
         Target.Offset(, 7).ClearContents
         Target.Offset(, 8).ClearContents
    End If

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Joe4]

Welcome to the Board!

See if these enhancements work better:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Range("S:S")) Is Nothing Then
        Application.EnableEvents = False
        If UCase(Target.Value) = "DONE" Then
            Target.Offset(0, 7).Value = Date
            Target.Offset(0, 8).Value = Environ("username")
        Else
            Target.Offset(0, 7).ClearContents
            Target.Offset(0, 8).ClearContents
        End If
        Application.EnableEvents = True
    End If
   
End Sub

 

[Jurajko]

Hello Jurajko,

I don't see a problem with your code. However, is it "Done" or is it "done"?
In this situation, your code is case sensitive.
If you've specified the target value as "Done", then that is how it needs to be typed into the target cell, not as "done".

You could overcome it being case sensitive as follows:-

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("S:S")) Is Nothing Then Exit Sub
   
    If Target.Value = "Done" Then
        Target.Offset(, 7).Value = Date
        Target.Offset(, 8).Value = Environ("username")
    Else
         Target.Offset(, 7).ClearContents
         Target.Offset(, 8).ClearContents
    End If

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Hi, thanks for your reply. The code is working well. My Problem is, that i get a Bug-Pop-Up Window everytime i insert or delete a row. But i find the way out:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Range("S:S")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "Erledigt" Then
            Target.Offset(0, 7).Value = Date
            Target.Offset(0, 8).Value = Environ("username")
        Else
            Target.Offset(0, 7).ClearContents
            Target.Offset(0, 8).ClearContents
        End If
        Application.EnableEvents = True
    End If
   
End Sub

 

[Jurajko]

Welcome to the Board!

See if these enhancements work better:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Range("S:S")) Is Nothing Then
        Application.EnableEvents = False
        If UCase(Target.Value) = "DONE" Then
            Target.Offset(0, 7).Value = Date
            Target.Offset(0, 8).Value = Environ("username")
        Else
            Target.Offset(0, 7).ClearContents
            Target.Offset(0, 8).ClearContents
        End If
        Application.EnableEvents = True
    End If
  
End Sub

Hi, thank you, i adjust it a bit, but it is working now:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Range("S:S")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "Erledigt" Then
            Target.Offset(0, 7).Value = Date
            Target.Offset(0, 8).Value = Environ("username")
        Else
            Target.Offset(0, 7).ClearContents
            Target.Offset(0, 8).ClearContents
        End If
        Application.EnableEvents = True
    End If
   
End Sub

 

[Joe4]

My Problem is, that i get a Bug-Pop-Up Window everytime i insert or delete a row. But i find the way out:

Yep, I posted that about a half-hour ago!

Did you use my code, or come up with almost the exact same code yourself?

 

[kevin9999]

@Joe4 Looks like the OP also came up with his own solution elsewhere... Excel VBA Macro Issue by inserting or deleting a row

 

[Joe4]

Jurajko,

Cross-posting (posting the same question in more than one forum) is not against our rules, but you must use the method of doing so covered by #13 of the Forum Rules.
If you do cross-post in the future, please follow these rules and provide the links amd then there shouldn’t be a problem.

 

[Jurajko]

Yep, I posted that about a half-hour ago!

Did you use my code, or come up with almost the exact same code yourself?

no, i used your code

 

[Joe4]

no, i used your code

OK, please take note of our rules on Cross-Posting, which I referenced in my previous post.
If you are going to post the same question in multiple places, you need to tell us and provide links to those other places!
Thanks