Looping through cell & generate Msg Box

FrancisM

Board Regular
Joined
Apr 12, 2016
Messages
139
I am trying to I am trying to write a macro to loop through a column (Z), & find the cells with a "Yes" in them. When a Yes is found I am trying to generate the following Msg “"Check to verify veteran data is entered in FY ## REFERALS. "It's critical that the veteran data is captured." & “You have entered No into cell (cell name).
Here is some background:
Cell Range F4:F10, F13:F17 (Yes/No) is linked to Cell Range X4:X10, X13:X17, & has the following formula in Column X (=F4="no"). If No is found TRUE is return value.
Cell Range I4:I10, I13:I17 has a Check box & is linked to Cell Range W4:W10, W13:W17 (Boolean answer).
Cell Range Y4:Y10, Y13:Y17 has this formula =AND(W4=TRUE, X4=TRUE).
Cell Range Z4:Z10, Z13:Z17 has this formula =IF(Y4, "Yes", "No")
Here is my current code:
Code:
Private Sub Worksheet_Change(ByVal target As Range)
im rng As Range, cell As Range
    Set rng = Range("Z:Z")
     For Each cell In rng
Next cell
    cell.Value = cell.Value
       'The code below is a reminder to enter data in the Referral Workbook.
     If Intersect(target, ActiveSheet.Range("Z:Z")) Is Nothing Then Exit Sub
 
     If target.Value <> "Yes" Then Exit Sub
 
     If ReferralsCalled = False Then
 
     'Shows a 3 line message box.
 
          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
End If
End Sub

Currently it hangs on: If ReferralsCalled = False Then
I don’t know if my code is correct. I know that target.Address is not correct. I used it elsewhere & have not fixed it yet. Please inform me what I am doing wrong.
Any help is appreciated.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I am not really sure if I understood your requirement exactly but would the below code work for you ?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, Rg As Range
Set Rg = ActiveSheet.Range("Z:Z")
    If Not Intersect(Target, Rg) Is Nothing Then
        If LCase(Target.Value) = "yes" 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 " & Replace(Target.Address, "$", ""), vbInformation, "Vocational Services Database"
        End If
    End If
End Sub
 
Upvote 0
No response or error message. The bottom line, is that I am trying to have the error message pop ug & the other file called when "Yes" is in Column Z. Here is a link to one drive for a sample. I have included your code.

HTML:
https://1drv.ms/x/s!Ak-4iXjPpsJMgSLMZ7BZUHuzG2Ew?e=o9N6t3
 
Upvote 0
Ok, how about replace my previous code with this

Code:
Dim x As Long
For x = 4 To 17
    If LCase(Cells(x, "Z").Value) = "yes" 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 " & Replace(target.Address, "$", ""), vbInformation, "Vocational Services Database"
    End If
Next

Just a quick question, are you handling the data in column W by VBA ? It is all FALSE without any formulas
 
Upvote 0
You cannot use a Change event for this, because Col Z is a formula & one of the cells it relies on (col W) is populated via a checkbox.
Change events are not triggered by formulae, or clicking a checkboxe.
 
Upvote 0
It did fire because as Fluff said change events don't work with formulas & it was based on column W( no formula).
 
Last edited:
Upvote 0
I had tried VBA to copy in Column Z into AA, & I did copy the values only. I as not successful in getting to run as a calculated change. The 1 question I have, is it possible to have it fire wit 'YES" response in AA.
 
Upvote 0
Change your code to look at col AA instead of Z.
 
Upvote 0
Fluff Thanks. However I was able to pinpoint the problem. I have code to copy the values from Column Z. I tested the code with 1 row of values & it worked. When I did standard range of cells it ran through the entire range, even if the value was No.
Here is the working code:
Code:
Private Sub Worksheet_Calculate()
'Copies values from ZZ into AA.
 Range("Z4:Z4").copy
  Range("AA4:AA4").PasteSpecial (xlPasteValues)
  
    Application.CutCopyMode = False
        
  End Sub

This is the code I said was causing the problem
Code:
Private Sub Worksheet_Calculate()
'Copies values from ZZ into AA.

 Range("Z4:Z10").copy
  Range("AA4:AA10").PasteSpecial (xlPasteValues)
  
    Application.CutCopyMode = False
    
   Range("Z13:Z17").copy

   Range("AA13:AA17").PasteSpecial (xlPasteValues)

   Application.CutCopyMode = False
    
  End Sub

I believe my problem is with my Worksheet_Calculate code, but I can't find it.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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