• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Have a msg box pop up based on cell text.

Frncis

Member
I am trying to have a msg box pop up when "Yes" is entered in range "G4:G10,G13:G17". However the code is not working, even when I list the range as one cell.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Worksheets("October_Meeting_#_1").Range("G4:G10, G13:G17").Value = "Yes" Then
       MsgBox ("Please Enter the SC or NSC status, if this is the first meeting of the new Fiscal!")
    End If
End Sub
I did Debug ( no error), test ran the code & no response. Please let me know wat I am doing wrong.
 

Marc L

Excel Ninja
As this event raises when a cell is modified, its code must be located in the worksheet module (so not in a standard one)
and no needs any worksheet reference for its own worksheet :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([G4:G10,G13:G17], Target) Is Nothing Then
        If Target.Value2 = "Yes" Then MsgBox ("Please Enter the SC or NSC status, if this is the first meeting of the new Fiscal!")
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

Frncis

Member
The original code was placed in the worksheet module. I was not able to get it to work. Is it possible to have call change in a change event?
MY supervisor also asked if I could make it a standard Yes/No Msg box. You just got to love changes in mid stream. I was think about making the new code a call batch. Call Last_Year Here is the new code:
Code:
Option Explicit
Sub Last_Year()
Dim Msg As String, Ans As Variant
'Application.Speech.Speak "Please check the date & year of the meeting dates this month,!!   correct as necessary", SpeakAsync:=True
'Application.Wait (Now + TimeValue("00:00:06"))
'Application.Speech.Speak " Do you want to set the Calendar to the new month?  ", SpeakAsync:=True
  Msg = "Is this veteran a carry ovfer from last year?"
   Ans = MsgBox(Msg, vbYesNo, "Vocational Services - Reminder " & ActiveSheet.Name)
   Select Case Ans
    Case vbYes
Range("I4").Select
    Case vbNo
End Select
End Sub
 

Marc L

Excel Ninja
The easy way is to place the 'Last Year' procedure in the same event / worksheet module …​
 

Frncis

Member
Dumb question. So you are saying to place the code in the worksheet change event. Correct?
 
Last edited by a moderator:

Marc L

Excel Ninja
If you really need to call it move the 'Last Year' procedure to the worksheet module where stands the Change event procedure …​
 

Frncis

Member
I Was thinking if I called it, I wouldn't have to place copies of the code on several sheets. But it doesn't matter to me, where it is placed. The bottom line is that I can't get the code you provide, or the new one I added, to work.
 
Last edited by a moderator:

Frncis

Member
If you really need to call it move the 'Last Year' procedure to the worksheet module where stands the Change event procedure …​
Marc. I found the issue. The file got corrupted, so none if the code ran. Also I checked & I can modify code that I currently have to accomplish what I need. I will post the finished code at a later date. You are a true gentleman, assisting others. Sorry I took your time.
 

Frncis

Member
Here is the current code that is located in Private Sub Workbook_SheetChange(ByVal Sh as Object, ByVal Target As Range) in ThisWorkbook
Code:
'The code below is a reminder to enter data in the Referral Workbook.

          If Intersect(Target, Sh.Range("G:G")) Is Nothing Then Exit Sub
              If Intersect (Target2, sh.Range("G;G)) Is Nothing Then Exit Sub
     If Target.Value <> "No" Then Exit Sub

     If referralscalled = False Then

     'Shows a 3 line message box.
                 Application.Speech.Speak " Please verify veteran data is entered. in. Fiscal Year  Referrals.  It's critical that, veteran data is captured.  ", SpeakAsync:=True
                    MsgBox "Check to verify veteran data is entered in FY ## REFERALS." & vbCr & _
                    "It's critical that the veteran data is captured." & vbCr & _
                    "You have entered No into cell" & Target.Address, vbInformation, "Vocational Services Database"
          
         Call Referals  ' Calls Referals folder.
               Call Tabs
     End If
   End Sub
It does work! I will accomplish what my supervisor wants in another way. Again thanks for your guidance.
 
Top