Reversing Current values in VBA code.

Question

Nothing to Lose  & I have been trying to call a file when No is entered in column G (Is this a Follow Up) & TRUE is in column W(Boolean value).  The first check box In Check In - CPRS Note is linked to column W. Cell range for both Column G & W is Row 4:10 & 13:17.  The code works if TRUE is in column W first; & then NO in column G. I am trying to reverse the order.  Below is a SC of  Columns G  (Is this a Follow Up), & H (Check In - CPRS Note). Here is the current code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Sh.Name

'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", "Sheet58"

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.

If Intersect(Target, Sh.Range("W:G")) Is Nothing Then Exit Sub

If Target.Value <> "No" Or Target.Offset(0, 16).Value <> True 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

 The underlined code was graciously provided by Nothing to Lose. It was an improvement in the original code, but not what we were trying to accomplish.  

Answer 1

Either W is not being changed, or ReferralsCalled is True: use this to troubleshoot:

    If Intersect(Target, Sh.Range("G:W")) Is Nothing Then

        MsgBox "No cells were changed in G to W"

        Exit Sub

    End If

    If Not Intersect(Target, Sh.Range("W:W")) Is Nothing Then

        If Sh.Cells(Target.Row, "G").Value = "No" And Target.Value = True Then

            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

            Else

                MsgBox "ReferralsCalled is currently ""True"""

            End If

        End If

    Else

        MsgBox "Changed cell was not in column W"

    End If

Answer 2

Correct. Column W is dependent on the user. I tried the code & nothing happened. I think it is because of where I placed it. Please review, & advise.  Here is the part of the code I changed:

'The code below is a reminder to enter data in the Referral Workbook.

If Intersect(Target, Sh.Range("W:G")) Is Nothing Then Exit Sub

**** If Not Intersect(Target, Sh.Range("W:W")) Is Nothing Then

If Sh.Cells(Target.Row, "G").Value = "No" And Target.Value = True Then

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

 I have tried replacing original line of code with yours, also I have tried adding the line, & nothing happened. I know I am missing something, but I don't know what.

Answer 3

Try adding a section like this - this assumes that column W is being changed by the action of the user

    If Not Intersect(Target, Sh.Range("W:W")) Is Nothing Then

        If Sh.Cells(Target.Row, "G").Value = "No" And Target.Value = True Then

            'code to call the file

        End If

    End If

Answer 4

I hope this is clearer. The 2 columns I am focusing on are Is this a follow Up(Column G), & Check In - CPRS Note (Column H).

Column H has 2 check boxes, [] & []. The first one is linked to Col umn W (Boolean answer),  Column G has "Yes/No".

 Currently Column W must have True first & then Column G must have No.

I am trying to have the code work were:  Column G has No, first; & column W has TRUE, & then the file is called.

Answer 5

I read your post but cannot figure out "what we were trying to accomplish"   Reversing current values...  it's not specific enough.

Tell us clearly:

When X is y and C is d then I want to do this....