Excel Need assistance with modifying a Duplicate declaration.

FrancisM

FrancisM

Joined
Dec 13, 2017
Messages
71
Reaction score
19
The difference between the working code & the new code is the cell range. In the working cell the Range is B4:B17, & B:B. Here is the working code:

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 this a follow up? " & vbCr, _
       vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
       If MsgBoxResult = vbYes Then
            Exit Sub
       ElseIf MsgBoxResult = vbNo Then
    MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
       vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)
      
            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

The new code Range is J4:J17 & J:J. The message does change, but I am more concerned about this line : Dim MsgBoxResult As Long.
What changes can I make to that line? Some other lines do use Dim, but it should not matter. If it does I will provide the lines.
 
try temporarily two things:
1. removing Option Explicit
2. remove Dim MsgboxResult As Long

Also let me see your code under Referals
 
I did what you suggested, & when I debugged, there was no error message, also it appeared not to run. Here is the code under referrals:
Code: 
Sub Referals()
    Dim Msg As String, Ans As Variant
    
     Msg = "Would you like to open the Referal folder?"
    
     Ans = MsgBox(Msg, vbYesNo, "Vocational Services - Career Link   " & ActiveSheet.Name)
    
     Select Case Ans
         Case vbYes
             Dim MyFolder As String
             MyFolder = "N:\MHBS\Education and Employment\VR Reports\VRU REFERALS"
             ActiveWorkbook.FollowHyperlink MyFolder
            
                        
         Case vbNo
             GoTo Quit:
     End Select
    
Quit:

End Sub
 
AmjiBhai said:
When I tried ...it works...please see the attachment
Click to expand...
I have no problem getting it to run when there is just the working code. The problem is when there is a second batch of similar code & the range is J4:J17 & J:J instead if B$:B17 & B:B.
 
Keeping the referals code as its ....here is the revised code....please try..


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
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)

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 If
End Sub
 
AmjiBhai said:
Keeping the referals code as its ....here is the revised code....please try..


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
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)

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 If
End Sub
Click to expand...
I tried it on a sample file & I received this error "Compile error -Variable not defined" on addr =
If possible I need to have 2 separate messages. I am not able to upload a sample, my employer does not allow zip files. The best I can offer is a PDF print screen of the worksheet. Hopes that helps
 

Attachments

Last edited:
FrancisM said:
I tried it on a sample file & I received this error "Compile error -Variable not defined" on addr =
If possible I need to have 2 separate messages. I am not able to upload a sample, my employer does not allow zip files. The best I can offer is a PDF print screen of the worksheet. Hopes that helps
Click to expand...
Yes! That's what I needed... With get back to you once I get back to my desk.

In the meantime please rerun my last code but first of all temporarily remove the command Option Explicit... and let me know if variable not defined error evaporates
 
Last edited:
Here is the revised 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
Dim addr As Variant
Dim Rng As Variant
Dim cRng As Variant
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)
cRng = addr & ":" & addr
If WorksheetFunction.CountA(Range(cRng)) <> 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 If
End Sub
 
AmjiBhai said:
Here is the revised 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
Dim addr As Variant
Dim Rng As Variant
Dim cRng As Variant
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)
cRng = addr & ":" & addr
If WorksheetFunction.CountA(Range(cRng)) <> 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 If
End Sub
Click to expand...
@[U]AmjiBhai[/U] thank you it does run. This code is going to be placed on the last few work sheets if the work book. Would it be possible to modify the code. I still want it run when a entry is made in column B, & to run when "Yes" is in Column J.
 

Attachments

FrancisM said:
@[U]AmjiBhai[/U] thank you it does run. This code is going to be placed on the last few work sheets if the work book. Would it be possible to modify the code. I still want it run when a entry is made in column B, & to run when "Yes" is in Column J.
Click to expand...

just one line added..If Range("J4") <> "Yes" Then Exit Sub ....as shown below

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Reminds the user to enter the carryover name into the current FY listing.
If Range("J4") <> "Yes" Then Exit Sub
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
Dim addr As Variant
Dim Rng As Variant
Dim cRng As Variant
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)
cRng = addr & ":" & addr
If WorksheetFunction.CountA(Range(cRng)) <> 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 If
End Sub
 
AmjiBhai said:
just one line added..If Range("J4") <> "Yes" Then Exit Sub ....as shown below

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Reminds the user to enter the carryover name into the current FY listing.
If Range("J4") <> "Yes" Then Exit Sub
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
Dim addr As Variant
Dim Rng As Variant
Dim cRng As Variant
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)
cRng = addr & ":" & addr
If WorksheetFunction.CountA(Range(cRng)) <> 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 If
End Sub
Click to expand...
It only runs if there is "Yes"
AmjiBhai said:
just one line added..If Range("J4") <> "Yes" Then Exit Sub ....as shown below

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Reminds the user to enter the carryover name into the current FY listing.
If Range("J4") <> "Yes" Then Exit Sub
Dim MsgBoxResult As Long
If Target.Count > 1 Then Exit Sub
Dim addr As Variant
Dim Rng As Variant
Dim cRng As Variant
addr = Mid(Target.Address, 2, 1)
If addr = "B" Or addr = "J" Then
Rng = addr & "4:" & addr & "17"
If Intersect(Target, Range(Rng)) Is Nothing Then Exit Sub
MsgBoxResult = MsgBox("Is this a follow up? " & vbCr, _
vbYesNo, "Vocational Services - OVR " & ActiveSheet.Name)
If MsgBoxResult = vbYes Then
Exit Sub
ElseIf MsgBoxResult = vbNo Then
MsgBoxResult = MsgBox(" It's critical that Carryover data is captured! " & vbCr, _
vbInformation, "Vocational Services - OVR " & ActiveSheet.Name)
cRng = addr & ":" & addr
If WorksheetFunction.CountA(Range(cRng)) <> 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 If
End Sub
Click to expand...
I just learned that it goes right to Call Referals & does not run the code below.
Code: 
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
I Also noticed that it does not respond to an entry in Column B, but that is Ok. Having it run on Column J is actually better. I did modify this line If Range("J4") <> "Yes" Then Exit Sub To respond to "Yes", "No" & "TBD". It does see "Yes" & "No", but not "TBD". Here is the code: If Range("J4") <> "Yes, No, TBD" Then Exit Sub Any advise.
 
Could you please fill-in a few lines of sample data so that I get clarity of what is actually intended ... We might have to redo all what we have done sofar.
 
Back
Top