userform errors

[rjmdc]

hi
i have a user form
somehow the delete row macro messes up the send email macro

Log Sheet.xlsm
	A	B	C	D	E	F	G	H	I
4	tom	12/27/2021	Submitted DDP1
5	jerry	January 04, 2022	Update SD Participants DPP Services
6	mark	January 04, 2022	Created Files for DDP1
Log

emails works but then when i delete row it debugs to the send email

Sub DeleteRow()
Dim tbl     As ListObject

    If Selection.Column <> 1 Or Selection.Cells.Count <> 1 Then
        MsgBox "You must be in Column A to perform the delete function."
        Exit Sub
    End If

    If MsgBox("Are you sure you want to delete: " & Selection.Value & "?", vbYesNo + vbExclamation, "Confirm Delete") = vbNo Then
        Exit Sub
    End If
       
'    Dim tbl As ListObject
    Dim LastRow As Range
    Dim col As Long
    Set tbl = Worksheets("Log").ListObjects("Service_Log_Sheet")
    
    
'    Application.EnableEvents = False
'    tbl.AutoFilter.ShowAllData

    Dim sr As Long 'Actual Row
    Dim slr As Long 'Start List Row
    sr = Selection.Rows(1).Row
    slr = sr - Selection.ListObject.Range.Row  'The starting List Row


'    Application.EnableEvents = True

    Selection.ListObject.ListRows(slr).Delete

End Sub

Private Sub Worksheet_Activate()
    frmLogSheet.Show
End Sub
Private Sub Worksheet_Deactivate()
    Unload frmLogSheet
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sMail As String, sSubj As String, sBody As String

  If Target.Column = 5 And Target.Value = ChrW(&H2713) Then
  
'      If MsgBox("Pressing OK will send email to notify", vbOKCancel + vbInformation, "Startup Approved") = vbOK Then
        
        'Data for MAIL 1 Column F
        sMail = "me@me"
        sSubj = Cells(Target.Row, "B").Value & " Budget Uploaded"
        sBody = "A Budget was uploaded for  " & Cells(Target.Row, "B").Value & "."
        Call SendMail(sMail, sSubj, sBody)
        
        MsgBox "Outlook messages sent", , "Outlook message sent" ' Confirm Sent Email
'      End If
    ElseIf Target.Column = 7 And Target.Value = ChrW(&H2713) Then
  
'      If MsgBox("Pressing OK will send email to notify", vbOKCancel + vbInformation, "Startup Approved") = vbOK Then
        
        'Data for MAIL 1 Column H
        sMail = "me@me"
        sSubj = Cells(Target.Row, "B").Value & " DDP1 Approved"
        sBody = "DDP1 was approved for  " & Cells(Target.Row, "B").Value & "."
        Call SendMail(sMail, sSubj, sBody)
        
        MsgBox "Outlook messages sent", , "Outlook message sent" ' Confirm Sent Email
'      End If
    End If
 End Sub

Sub SendMail(sMail, sSubj, sBody)
  Dim OutlookApp As Object
  Set OutlookApp = CreateObject("Outlook.Application").CreateItem(0)
  With OutlookApp
    .to = sMail
    .Subject = sSubj
    .Body = sBody
    .Display 'Display Email
    .Send 'Send Email
  End With
End Sub

 

[rjmdc]

now it fails at
Sub ShowLogForm()
If Worksheets("Log").Activate = True Then
frmLogSheet.Show
End If
End Sub

 

[rjmdc]

thanks
it works
i appreciate the help

 

[Siddharth Rout]

now it fails at
Sub ShowLogForm()
If Worksheets("Log").Activate = True Then
frmLogSheet.Show
End If
End Sub

Now that is a diiferent error for a different reason. it depends on what you want to achieve here. I will cover 2 scenarios that I think you may be interested in

SCENARIO 1

Launch frmLogSheet when the Log sheet is activated. In such a case replace

Sub ShowLogForm()
    If Worksheets("Log").Activate = True Then
        frmLogSheet.Show
     End If
End Sub

by

Private Sub Worksheet_Activate()
    frmLogSheet.Show
End Sub

I am assuming all your code is in the Sheet Code Module.

SCENARIO 2

Launch frmLogSheet only if Log sheet is active. In such a case replace

Sub ShowLogForm()
    If Worksheets("Log").Activate = True Then
        frmLogSheet.Show
     End If
End Sub

by

Sub ShowLogForm()
    If UCase(Trim(ActiveSheet.Name)) = "Log" Then
        frmLogSheet.Show
    End If
End Sub

 

[rjmdc]

hi
no more errors
thanks it works
i really appreciate