Vba Code not working when value is changed from one cell to another.

[Frncis]

The current code below:

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

Runs as expected, however, if F:F & No is changed to Z:Z & Yes. It does not hang up, but does not give any response. Could you explain what I am doing wrong?
I am certain it is something very simple, that i am missing.

 

[Chihiro]

Please use Code tag when posting codes. I've updated it this time.

Also, without knowing the code for Referals sub and what sets the value for ReferralsCalled variable/function, we can't really help you.

I'd recommend uploading sample workbook with desensitized info.

 

[Frncis]

The code is on thisworkbook, & the other parts of the puzzle is on October #1 worksheet.


Also in the future I will be more careful which form I post on.

 

[Chihiro]

Works fine for me.

'The code below is a reminder to enter data in the Referral Workbook.
     If Intersect(Target, Sh.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

However, you have formula in Z. Code will not trigger with formula, cell value must be changed manually.

If you need to fire code based on formula then you'd need to use Worksheet_Calculate event.

 

[Frncis]

I am not sure of the entire syntax for worksheet calculate & would welcome some education.
I think some thing along these lines.

As You wrote #3 Reply
Also in the future I will be more careful which form I post on.

Private Sub Worksheet_Calculate()
If Range("Z:Z").Value = 1 then

End If
End Sub

I think that you would want the answer in column AA (next column) & then change the column on thisworkbook.
What I don't understand the language that goes between "IfRange & End If.

 

[Chihiro]

So, unlike Worksheet_Change, Worksheet_Calculate can't check which cell's value changed.

There are workaround, but with your workbook's set up...

I'd just tie the code to change event on W column. Since that's updated based on checkbox selection... Or directly to Checkbox selection. Just add a code to check that F column isn't blank when checkbox is clicked.

 

[Frncis]

I am thinking that I could copy only the value of a cell in the range of (Z4:Z10,Z13:Z17) to the next cell (AA4:AA10,AA13:AA17) and then used "AA" as the column to call the other file. I have found some code to copy cell values without a module. When I tested the code, I got no error when it was complied, but I also did not get any type of response.

 

[Chihiro]

That still won't pinpoint the cell you want to check for. As I mentioned, you need to set something up to trigger when precedent to Z column is updated manually. Be it cell or checkbox.

But then you need to ensure all info necessary is filled at that stage as well. Unfortunately, I can't check all your code, as you have libraries in there which I do not have and likely incompatible with 64 bit install of Excel.

 

[Frncis]

I deleted all worksheets (see attached), except 1 & deactivated the code calling the other file (code located on the workbook). I have also tried this code & placed it on the worksheet:

Private Sub Worksheet_Calculate()
'If Not Intersect(target, Range("Z:Z")) Is Nothing Then
   
    '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

It keeps haging up on 'If Not Intersect(TARGET......... with the following error: Variable not defined.

 

[Frncis]

I got it to copy the value of a cell in a range with this code:

Private Sub Worksheet_Calculate()
Sheets("October_Meeting_#_1").Range("Z4:Z10").copy
 Sheets("October_Meeting_#_1").Range("AA4:AA10").PasteSpecial xlPasteValues
 End Sub

However it is static. How would I code it so, as other cells change it would copy the changes?
I figured I would work this problem & the message & calling a file.

 

[Frncis]

I got it to copy & when I debug it hangs up on If Intersect(TARGET..... & I don't know why. Any suggestion? Here is the code:

Private Sub Worksheet_Calculate()
 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
 
 'If Not Intersect(target, Range("Z:Z")) Is Nothing Then
    
    '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

 

[Frncis]

I found out that, If Intersect(target.... is a function of sheet change, & therefore won't won't work with worksheet calculate.