Excel Message box called if 1 of 3 options is entered.

[FrancisM]

@AmjiBhai I think that 3 separate batches of code are required. 1 for beginning of the Fiscal Year (Sub_New_FY_Referrals - works fine ); 1 for the normal Referrals (Referals - misspelled on purpose); & End of Year (FY_End_Referrals). I posted the screen shot sample of the FY_End_Referrals. I need the msg box to fire when any of the entries shown are in a range J4:J17. Here is a sample of the message that is common to all 3. I will continue working on the normal referrals tomorrow.

   MsgBoxResult = MsgBox("Is the Veteran a carryover from last year or a new client? " & vbCr, _  'Is the Veteran a carryover from last year or a new client (changes depending on which batch it is)
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
            Exit Sub
       ElseIf MsgBoxResult = vbYes Then
            If WorksheetFunction.CountA(Range("B:B")) <> 0 Then
                MsgBox " Check to verify Veteran data is entered in FY ##  Referrals" & vbCr & _
                   " It's critical that Carryover data is captured. " & vbCr & _
                   "" & vbCr & _
                   " Please enter the name in walk in list if not on either last " & vbCr & _
                   " year's or this year's consult list! " & vbCr & _
                   "" & vbCr & _
                   " Enter veteran as a walk in, if there was a consult from last year and " & vbCr & _
                   " enter the SC percent" & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 2) & " in cell " & Target.Address, vbInformation, "Vocational Services - Career Link" & ActiveSheet.Name

I am sorry I put you through so much trouble.

 

[AmjiBhai]

No Problem! It's my pleasure if I can be of any service to nice people like you..
Please let me have details of what this code is all about which will help bridge the communication gap...
In order to understand your pdf file(the screen shot) I need clarifications what is each essential column's purpose.
The column Headings ....A,B,C,D,E,F are not appearing in the screen shot making it difficult for me to guess the column heading.

Partial VBA Code don't help. Please provide the code which starts with Sub and ends with End Sub.

 

[FrancisM]

No Problem! It's my pleasure if I can be of any service to nice people like you..
Please let me have details of what this code is all about which will help bridge the communication gap...
In order to understand your pdf file(the screen shot) I need clarifications what is each essential column's purpose.
The column Headings ....A,B,C,D,E,F are not appearing in the screen shot making it difficult for me to guess the column heading.

Partial VBA Code don't help. Please provide the code which starts with Sub and ends with End Sub.

Sorry. I should have known better. I think my previous attempts just made it more complicated. Heading Titles: A (None); B ( Name); C (last 4); D (Contact); E (Information); F (Contacted-CPRS); G (Check In); H (Is this a Follow up?); I (CPRS); J (Is a follow up requested?) Both the code for the new Fiscal year & normal Referrals, use the same code with some minor modification in the message & use B ( Name) . Here is the code for the new Fiscal year & normal Referrals:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Reminds the user to enter the carryover name into the current FY listing.
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B4:B17")) Is Nothing Then Exit Sub
       MsgBoxResult = MsgBox("Is the Veteran a a new client to Career Link? " & vbCr, _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
            Exit Sub
       ElseIf MsgBoxResult = vbYes Then
            If WorksheetFunction.CountA(Range("B:B")) <> 0 Then
                MsgBox " Check to verify Veteran data is entered in FY ##  Referrals! " & vbCr & _
                   "" & vbCr & _
                   " It's critical that Veteran data is captured. " & vbCr & _
                   "" & vbCr & _
                   " Please enter the name in walk in list if not on the consult list! " & vbCr & _
                   "Do not enter the name on the Walk In list of there is a Consult! " & vbCr & _
                   "" & vbCr & _
                   " Enter veteran as a walk in, if there was no consult this year and enter the SC percent! " & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 2) & " in cell " & Target.Address, vbInformation, "Vocational Services - Career Link" & ActiveSheet.Name
            
                Call Referals 'Calls Referrals folder.
            Else
                Exit Sub
                End sub

If Column J is used to call the massage box & file, it should run when there is "Yes, No, TBD". I don't think a Yes/No message box is necessary for end of the year referrals. Probably a simple message & using this line of code: Call Referals 'Calls Referrals folder.

 

[FrancisM]

@AmJiBhaiI
I had the code working for the end of year referrals. However, after I modified the message, it gave me a runtime error '13 message (Type mismatch) on

MsgBoxResult = MsgBox("Is the Veteran meeting again? " & vbCr, _
       "" & vbCr & _
       "Click NO for anything other than YES" & vbCr & _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)

Here is the entire code:

'Reminds the user to enter the carryover name into the current FY listing.
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("J4:J17")) Is Nothing Then Exit Sub
       MsgBoxResult = MsgBox("Is the Veteran meeting again? " & vbCr, _
       "" & vbCr & _
       "Click NO for anything other than YES" & vbCr & _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
            Exit Sub
       ElseIf MsgBoxResult = vbYes Then
            If WorksheetFunction.CountA(Range("J:J")) <> 0 Then
            
            
            
            
                MsgBox " It's critical that Carryover data is captured. " & vbCr & _
                   "" & vbCr & _
                   " Check to verify Veteran data is entered in FY ##  Referrals" & vbCr & _
                   " If the entry is Yes! " & vbCr & _
                   "" & vbCr & _
                   " Please enter the name in walk in list if not on either last " & vbCr & _
                   " year's or this year's consult list! " & vbCr & _
                   "" & vbCr & _
                   " Enter veteran as a walk in, if there was a consult from this  year and " & vbCr & _
                   " enter the SC percent" & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 10) & " in cell " & Target.Address, vbInformation, "Vocational Services - Career Link" & ActiveSheet.Name
            
                Call Referals 'Calls Referrals folder.
            Else
                Exit Sub
            End If
      End If

It is probably very simple that I am forgetting. Could you please review & let me know.

 

[FrancisM]

Problem solved. Here is the finished code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Reminds the user to enter the carryover name into the next FY listing.
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("J4:J17")) Is Nothing Then Exit Sub
       MsgBoxResult = MsgBox("Is the Veteran meeting again with Career Link? " & vbCr, _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
            Exit Sub
       ElseIf MsgBoxResult = vbYes Then
            If WorksheetFunction.CountA(Range("J:J")) <> 0 Then
                MsgBox " Please enter all Veteran data in entered in FY ##  Referrals! " & vbCr & _
                   "" & vbCr & _
                   " It's critical that Veteran data is captured. " & vbCr & _
                   "" & vbCr & _
                   " If the veteran is meeting again in the next FY! " & vbCr & _
                   " Enter the name on the Walk In list even if there is a" & vbCr & _
                   "Consult during this FY! " & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 10) & " in cell " & Target.Address, vbInformation, "Vocational Services - Career Link" & ActiveSheet.Name
            
                Call Referals 'Calls Referrals folder.
            Else
                Exit Sub
                End Sub