1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modifying sheet change to include a Text & Bolean answer.

Discussion in 'VBA Macros' started by Frncis, Feb 5, 2019.

  1. Frncis

    Frncis Member

    Messages:
    74
    This code below currently runs when there is a "No" response in cell Range H:H

    Code (vb):

    If Intersect(Target, Sh.Range("G:G")) Is Nothing Then Exit Sub
         If Target.Value <> "No" Then Exit Sub
         If ReferralsCalled = False Then
     
    I simplified an earlier post with the above. This is based only on a NO response.
    The code below, I think should work on a numeric response.
    Code (vb):
     If Intersect(Target, Sh.Range("Y:Y")) Is Nothing Then Exit Sub
        If Target.Value <> "1" Then Exit Sub
        If ReferralsCalled = False Then
    I am I correct & how do you nest on inside the other? The user would enter NO on one entry & then Click a box that give a Boolean answer, which is converted into a numeric response.
    Last edited: Feb 5, 2019
  2. Frncis

    Frncis Member

    Messages:
    74
    I am trying to call a file when both No and a box is checked. No is found in range G4:G17. I have a formula to change a Boolean answer to a number. Here is the formula =AND(W4)+0, it is located in column X. Here is the code I came up with.
    Code (vb):
      'The code below is a reminder to enter data in the Referral Workbook.
       If Intersect(Target, Sh.Range("G:G")) Is Nothing Then Exit Sub
        If Target.Value <> "No" Then Exit Sub
        If ReferralsCalled = False Then
            Else
            If Intersect(Target, Sh.Range("x:x")) Is Nothing Then Exit Sub
        If Target.Value <> "1" 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, "Career Link Meeting List"
       
            Call Referals
        End If
        End If
     
    When I run debug, there is no errors, so the syntax is correct. However there is no apparent action. The code does run if I don't include the code for X:X. This is my first attempt at combining tis type of code. Please let me know what I need to change
  3. Frncis

    Frncis Member

    Messages:
    74
    With the help of a friend here is the working code.

    Code (vb):
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Select Case Sh.CodeName
        '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", "Sheet82"
         
                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.
       Application.ScreenUpdating = False
        Dim lastRow As Long
        Dim cell As Range
        lastRow = Range("G" & Rows.Count).End(xlUp).Row
      Dim Chk As Boolean
      For Each cell In Range("H4:H10, H13:H17")
          If LCase(cell.Value) = "no" Then
            If Target.Value = "No" And Target.Offset(, 15).Value = True 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" & Target.Address, vbInformation, "Career Link Meeting List"
                Chk = True
                Exit For
                    End If
          End If
      Next
    If Chk Then Call Referals
    Application.ScreenUpdating = True
    End Sub
     

Share This Page