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:
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
Group 1 does run as expected, but then goes to group 2.
Here I the 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
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?