Requiring popup message to run after 2 conditions are met.

FrancisM

Board Regular
Joined
Apr 12, 2016
Messages
139
I am trying to get a pop up message to run if the following 2 conditions are met "No" & "True". No is in cell range G4:G10, G13:G17; True is located in W4:W10, W13:W17. However true is a Boolean answer. I do have a formula located in cell range x4:x10, x13:x17 that concerts the Boolean answer to a 1 or 0. I thought that you had to convert a Boolean answer to a numeric answer to use in code.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     'The code below is a reminder to enter data in the Referral Workbook.
     Application.ScreenUpdating = False
     Dim lastRow As Long
     Dim cell As Range
     lastRow = Range("G" & Rows.Count).End(xlUp).Row


For Each cell In Range("G4:G10, G13:g17" & lastRow)
    If InStr(1, cell.Value, "No") <> 0 Then
        If InStr(1, cell.Offset(, 17).Value, "1") <> 0 Then
        End If
    End If
Next


Application.ScreenUpdating = 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, "Career Link Meeting List"
   
        Call Referals
    
  
 End Sub
When I run the code it sees the "no" & runs the message, & calls Referals. I have are 3 questions.
1.Do you have to convert Boolean answers to numeric?
2. How do you get excel to see the second condition?
3. Could you,explain what I am doing wrong?
 
it works!!!

Monday I will see if I can reverse the order. Here are my reasons. The user will enter the name, etc for a new entry. At a late date the box will be checked. I will keep you posted.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
OK, unfortunately change events do not get triggered by checkboxes.
 
Upvote 0
You are correct. What I did was to reverse the order of columns. that solved the problem. Since we had several messages. I posted the working code that you provided, for future users.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'The code below is a reminder to enter data in the Referral Workbook.
     Application.ScreenUpdating = False
     Dim lastRow As Long
     Dim cell As Range
     lastRow = Range("G" & Rows.Count).End(xlUp).Row

   For Each cell In Range("H4:H10, H13:H17")
      If LCase(cell.Value) = "no" Then
         If Target.Value = "No" And Target.Offset(, 15).Value = True Then
            MsgBox "You have No & true"
         End If
      End If
   Next
Application.ScreenUpdating = True
 End Sub
Than you for your assistance & being so patient with me.
 
Upvote 0
Glad it's working & thanks for the feedback
 
Upvote 0
I left a piece of code out in the previous response.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    Select Case Sh.CodeName
    'These are the worksheets here that are not to be called with change
         Case "Sheet1", "Sheet11", "Sheet21", "Sheet31", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet 45", "Sheet46", "Sheet47", "Sheet48", "Sheet49", "Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", "Sheet56", "Sheet57", "Sheet82"
         
             Exit Sub
     End Select
     
     
     If Not (Application.Intersect(Target, Sh.Range("B4:B10, B13:B17")) _
       Is Nothing) Then   'Note that Range is now identified with the calling Sheet object variable (Sh)
         With Target
             If Not .HasFormula Then
                 Application.EnableEvents = False
                 .Value = UCase(.Value)
                 Application.EnableEvents = True
             End If
         End With
     End If
     'The code below is a reminder to enter data in the Referral Workbook.
     Application.ScreenUpdating = False
     Dim lastRow As Long
     Dim cell As Range
     lastRow = Range("G" & Rows.Count).End(xlUp).Row




   For Each cell In Range("H4:H10, H13:H17")
      If LCase(cell.Value) = "no" Then
         If Target.Value = "No" And Target.Offset(, 15).Value = True Then
            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, "Career Link Meeting List"
                 Call Referals
         End If
      End If
   Next


Application.ScreenUpdating = True


 End Sub
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top