Please Please someone help me i'm gon to be mad

[magicgood]

I trying to make a project to my work but I stuck now with Vb for like a week every time I fix a problem I faced a new one until now I face a problem I can't solve it I will attach the file and the problem when I try to add a new row or delete a row it's case error
link to my file :

my code in worksheet section
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then

ElseIf Range("E" & Target.Row) <> "" Then
Range("G" & Target.Row) = "In Progress"
Else
Range("g" & Target.Row) = ""
End If

If Not Application.Intersect(Range("H:H"), Range(Target.Address)) _
Is Nothing Then
Dim myToAdd As String

If Target.Column = 8 Then
If Target.Value = "" Then Exit Sub
If Target.Value = "Test1" Then
myToAdd = "Test1@test1.com"
ElseIf Target.Value = "test2" Then
myToAdd = "Test2@test2.com"

'Else
'etc.....
End If
If MsgBox("Do you want to send EMAIL to " & Target & " ?", vbYesNo + vbQuestion, "Confirm Sending Email") = vbNo Then Exit Sub
End If


With CreateObject("Outlook.Application").CreateItem(0) '0 will create a new email item
.To = myToAdd
.CC = ""
.Subject = "You Have a new ( Activities / Tasks / Items )" & " " & Target.Offset(0, -3).Value & " " & "assigned to you"
.Body = "Dear," & " " & Target & vbNewLine & vbNewLine & "You Have a new ( Activities / Tasks / Items )" & " " & Target.Offset(0, -3).Value & " " & "assigned to you Please follow up till have it Closed or assigned to the responsible ." & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "Keep it up" & vbNewLine & "STANDARDIZATION & COMPLIANCE"
.display 'Change this to .Send
End With

End If

End Sub


code in work book section
Private Sub Workbook_Open()
ActiveWindow.Zoom = 100
End Sub

code in module
Option Explicit

Private Sub CommandButton1_Click()
addNewRow
End Sub

Sub addNewRow()

' Do not insert a row before the first row.
Dim iTopRow As Integer
iTopRow = 5

If (ActiveCell.Row > iTopRow) Then

' Get the active row number.
Dim rowNum As Integer
rowNum = ActiveCell.Row

Rows(rowNum).EntireRow.Insert ' Insert a new row.

' Change the Codes (in first column).
Cells(ActiveCell.Row, 1) = rowNum - 4 ' For the active cells.

Dim iTotalRows As Integer ' Get the total used range rows.
iTotalRows = ActiveSheet.UsedRange.Rows.Count

Dim iRows As Integer

For iRows = rowNum + 1 To iTotalRows
Cells(iRows, 1) = iRows - 4
Cells(Application.ActiveCell.Row, 7).Select
Next iRows
End If
End Sub



any help will be more than appropriation

 

[hernantorres23]

Hi, put your data example using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

Also you can use Dropbox, Drive, etc.

 

[magicgood]

Hi, put your data example using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

Also you can use Dropbox, Drive, etc.

sorry for that here is my file in one drive

Microsoft OneDrive

 

[hernantorres23]

Thanks, now you will find more helpers.

 

[Michael M]

Does this work for you.....

Sub addNewRow()
    
    ' Do not insert a row before the first row.
    Dim iTopRow As Integer
    iTopRow = 5
    
    If (ActiveCell.Row > iTopRow) Then

        ' Get the active row number.
        Dim rowNum As Integer
        rowNum = ActiveCell.Row
 Application.EnableEvents = False
        Rows(rowNum).EntireRow.Insert       ' Insert a new row.
        
        ' Change the Codes (in first column).
        Cells(ActiveCell.Row, 1) = rowNum - 4       ' For the active cells.
        
        Dim iTotalRows As Integer   ' Get the total used range rows.
        iTotalRows = ActiveSheet.UsedRange.Rows.Count
        
        Dim iRows As Integer
        
        For iRows = rowNum + 1 To iTotalRows
            Cells(iRows, 1) = iRows - 4
            Cells(Application.ActiveCell.Row, 7).Select
        Next iRows
    End If
 Application.EnableEvents = True

End Sub

 

[yky]

I don't know why you put code in the Worksheet_Change event. Do you want to send email every time the worksheet changes?

 

[Trebor76]

Note this has been cross-posted here and here.

 

[Macropod]

magicgood, please read Mr Excel's policy on Cross-Posting in rule 13: Message Board Rules

 

[Michael M]

In that case I'm outta this thread....

 

[magicgood]

In that case I'm outta this thread....

yes i post the same question on several forums in the hope of getting a response quicker and i think this not a guilty to hope have my response quicker and if that i wasn't know