Share via

Reversing Current values in VBA code.

Anonymous
2019-05-31T13:17:21+00:00

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.  

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

16 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-05-31T17:33:00+00:00

    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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-05-31T14:58:15+00:00

    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.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-05-31T14:26:28+00:00

    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

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-05-31T14:10:53+00:00

    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.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2019-05-31T13:44:55+00:00

    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....

    Was this answer helpful?

    0 comments