Excel Msgbox runing beyond posted cell range.

FrancisM

FrancisM

Joined
Dec 13, 2017
Messages
71
Reaction score
19
I have code that runs when range "B:B" changes. However, when there is any change on the worksheet, it runs. I am trying to limit it to "B:B". Here is the code:
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  'Reminds the yser to enter the carryover name into the current FY listing.
Dim MsgBoxResult As Long
       MsgBoxResult = MsgBox("Is the Veteran a carryover from last year? " & vbCr, _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
       Exit Sub
ElseIf MsgBoxResult = vbYes Then
       If WorksheetFunction.CountA(Range("B:B")) <> 0 Then
        MsgBox " Check to verify Veteran data is entered in FY ##  Referrals" & vbCr & _
               " It's critical that Carryover data is captured. " & vbCr & _
               "" & vbCr & _
               " Please enter the name in walk in list if not on either last " & vbCr & _
               " year's or this year's consult list! " & vbCr & _
               "" & vbCr & _
               " Enter veteran as a walk in, if there was a consult from last year and " & vbCr & _
               " enter the SC percent" & vbCr & _
               "" & vbCr & _
               " You have entered " & Cells(Target.Row, 2) & " in cell " & Target.Address, vbInformation, "Vocational Services - OVR" & ActiveSheet.Name
          
           Call Referals 'Calls Referrals folder.
        Else
        Exit Sub
        End If
        End Sub
Any ideas, how I can have it run only on range "B:B"?
 
Problem solved. A friend pointed out that I left out a cell range.
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 'Reminds the user to enter the carryover name into the current FY listing.
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B4:B17")) Is Nothing Then Exit Sub
       MsgBoxResult = MsgBox("Is the Veteran a carryover from last year? " & vbCr, _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbNo Then
            Exit Sub
       ElseIf MsgBoxResult = vbYes Then
            If WorksheetFunction.CountA(Range("B:B")) <> 0 Then
                MsgBox " Check to verify Veteran data is entered in FY ##  Referrals" & vbCr & _
                   " It's critical that Carryover data is captured. " & vbCr & _
                   "" & vbCr & _
                   " Please enter the name in walk in list if not on either last " & vbCr & _
                   " year's or this year's consult list! " & vbCr & _
                   "" & vbCr & _
                   " Enter veteran as a walk in, if there was a consult from last year and " & vbCr & _
                   " enter the SC percent" & vbCr & _
                   "" & vbCr & _
                   " You have entered " & Cells(Target.Row, 2) & " in cell " & Target.Address, vbInformation, "Vocational Services - OVR" & ActiveSheet.Name
             
                Call Referals 'Calls Referrals folder.
            Else
                Exit Sub
            End If
      End If
        
        End Sub
 
Back
Top