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

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

Frncis

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

Code:
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:
 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:
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:
  '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
 
With the help of a friend here is the working code.

Code:
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
 
Back
Top