VBA running out side of stated cell range.

[Frncis]

I have code that is calls a message & opens a folder in another location when "No" is entered in range "H4:H10, H13:H17". However when No is entered in range "J4:R17" the code runs again. What is the best way to preventing the code from running outside of H4:H10, H13:H17".

Option Explicit

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.

      Application.ScreenUpdating = False

      Dim lastRow As Long

      Dim Cell As Range

      lastRow = Range("H" & 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, "Vocational Services - Career Link   " & ActiveSheet.Name




             Chk = True

             Exit For

                End If

      End If

   Next

If Target.Value = "No" Then Call Referals

Application.ScreenUpdating = True

End Sub

I tried

 For Each Cell In Range("H4:H10, H13:H17")

      If LCase(Cell.Value) = "no" Then

& was told that the variable is already in use. I understand why, but don't know how to overcome the earlier stated problem.

 

[Frncis]

Problem solved.
Here is the new code:

Option Explicit

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.
      Application.ScreenUpdating = False
      Dim lastRow As Long
      Dim Cell As Range
      lastRow = Range("H" & Rows.Count).End(xlUp).Row
   Dim Chk As Boolean
   If Target.Column = 8 Then
   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, "Vocational Services - Career Link   " & ActiveSheet.Name

             Chk = True
             Exit For
                End If
      End If
   Next
 If Target.Value = "No" Then Call Referals
Application.ScreenUpdating = True

      'Hide sheet tabs
    'ActiveWindow.DisplayWorkbookTabs = False
    End If
 End Sub

However, I don't understand why it works. Could you please explain how & why it limits the code to this range ("H4:H10, H13:H17")?

 

[NARAYANK991]

Hi ,

The revised code works because of this additional line of code :

If Target.Column = 8 Then

This checks that the code after this line will be executed only if the column where a change was made was column H ; Starting from column A as #1 , column H is #8.

However , the better way to ensure that only the cells of interest trigger code execution is to have the following line of code as the first line in your procedure :

If Application.Intersect(Target, Range("H4:H10, H13:H17")) Is Nothing Then Exit Sub

Narayan

 

[Frncis]

Hi ,

The revised code works because of this additional line of code :

If Target.Column = 8 Then

This checks that the code after this line will be executed only if the column where a change was made was column H ; Starting from column A as #1 , column H is #8.

However , the better way to ensure that only the cells of interest trigger code execution is to have the following line of code as the first line in your procedure :

If Application.Intersect(Target, Range("H4:H10, H13:H17")) Is Nothing Then Exit Sub

Narayan

2 thank you' s. 1) for explaining why it works & 2) for providing a simpler/better way.