I already have code using If Not Intersect(Target...., so I can't use that again. I have 2 groups of code for the same cell range, using "If not Application.Intersect", when 1 runs the other runs also. I think I may have to use different code to have only 1 group of code run.
Here is the 1st group of code:
Group 1 does run as expected, but then goes to group 2.Option Explicit ' This works on the row, M when pending is entered, it also opens the users Outlook calendar. ' Pending #1. Example #1 Set KeyCells = Range("M3:M329") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Application.Speech.Speak "Schedule two. appointments on your calendar. The first appointment. is a reminder. to send a contact letter. (if no response from the Phone call). Use the Red date to the right. The second appointment. is a reminder. two weeks later. to cancel the consult. if NO response from earlier attempts.", SpeakAsync:=True Application.Wait (Now + TimeValue("00:00:2")) VBA.MsgBox "Schedule two appointments on your calendar. The first appointment is a reminder to send a contact letter (if no response from Phone call.) Use the Red date to the right. The second appointment is a reminder two weeks later to cancel the consult, if NO response from earlier attempts.", vbOKOnly + vbInformation, _ "Vocational Services Reminder" 'Opens Outlook appointment Calendar. Dim olApp As Object ' Outlook.Application Set olApp = CreateObject("Outlook.Application") olApp.Session.GetDefaultFolder(olFolderCalendar).Display End If
Here I the code for group 2:
This group of code runs, but does not clear contents, & then goes to code group 1. Am I correct in thinking that I have to find different code for group 2?' This works on the row, M when pending is deleted, it also opens the users Outlook calendar. ' Pending #2. Set KeyCells = Range("M3:M329") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Dim Ans As Integer Ans = MsgBox("Two appointments were scheduled on your calendar previously. One for a Contact Letter, one to Cancel the Consult " & vbCrLf & vbNewLine & "Click Yes to delete the future appointments,if veteran contacted you. Click No, if there was no contact from the veteran.", vbYesNo, "Vocational Services Database - " & ActiveSheet.Name) Select Case Ans Case vbYes '[code if Ans is Yes]... 'Opens Outlook appointment Calendar. Dim olApp2 As Object ' Outlook.Application Set olApp2 = CreateObject("Outlook.Application") olApp.Session.GetDefaultFolder(olFolderCalendar).Display Range("$M$3:$M$329").ClearContents Case vbNo ' ...[code if Ans is No]... MsgBox " Enter the reason in the Column. You can choose from the drop down list or enter a new one.", vbInformation, "Vocational Services Database - " & ActiveSheet.Name ActiveCell.Offset(0, -1).Select End Select Exit Sub End If End Sub End Sub





Reply With Quote
