Modified macro code not working as expected.

Question

I started with basic code to call another file. Here is the 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.

If Intersect(target, Sh.Range("F:F")) Is Nothing Then Exit Sub

If target.Value <> "No" 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

It worked as expected, then I had to modify it, so if there was "Yes" in AA4, the code would run.

I added code to copy values into Column AA. from ZZ, & then the code shown above.

The new lines of code worked as expected regarding the copy, & then  I ran into a problem with the modified code, it hung up on this specific line. 

If Intersect(target, Sh.Range("F:F")) Is Nothing Then Exit Sub

Could you tell me the reason for the error, & how to fix it?

Here is the current code:

Private Sub Worksheet_Calculate()

'Copies values from ZZ into AA.

Range("Z4:Z10").copy

Range("AA4:AA10").PasteSpecial (xlPasteValues)

Range("Z13:Z17").copy

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

Application.CutCopyMode = False

If Intersect(target, Sh.Range("AA:AA")) 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 If

End Sub

Here is a link to a sample:

https://1drv.ms/x/s!Ak-4iXjPpsJMgR0YxKt43Tv\_hEVk?e=9FHV4L

Answer 1

No I don't have "Public ReferralsCalled As Boolean". 

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, The following Columns have formulas in them; W,X,Y,Z.

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  the 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")

I hope I am clearer.

Answer 2

Somewhere in your project you should have a declaration at the top of a module:

Public ReferralsCalled As Boolean

If it is inside of a subroutine or function then it won't truly be public.

Nothing about your code makes sense - describe in words what you want it to do - what cell you are manually changing, what cell(s) need to be evaluated, what cells have formulas, what cells need to be converted to values, etc.

Answer 3

Am I heading in the right direction? 

 Here is my current code:

Private Sub Worksheet_Change(ByVal target As Range)

Dim 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

It hangs up on " If ReferralsCalled = False Then**".** (variable not defined)

Answer 4

Thank you for explaining Target objects.  Columns W,X,Y & A have fromuls but Column AA does not. Value is copied from Column Z. 

Now I will keep working on a solution to call a file &  allow a message box.

Answer 5

The worksheet calculate event does not create a Target object - only the workhsheet change events do that, so you cannot use Target in the event as your show. 

All the cells on the sheet are calculated if they have formulas - you need to check the cells of interest by looping through them, or use the change event to limit your code to a specific row based on the row in which the change occured. Otherwise, all the formulas will be evaluated.