Excel Need assistance with modifying a Duplicate declaration.


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

Advertisements

Joined
Feb 21, 2018
Messages
209
Reaction score
83
try temporarily two things:
1. removing Option Explicit
2. remove Dim MsgboxResult As Long

Also let me see your code under Referals
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
When I tried ...it works...please see the attachment
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.
 
Joined
Feb 21, 2018
Messages
209
Reaction score
83
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
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
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:
Joined
Feb 21, 2018
Messages
209
Reaction score
83
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
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:
Joined
Feb 21, 2018
Messages
209
Reaction score
83
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
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
@[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

Joined
Feb 21, 2018
Messages
209
Reaction score
83
@[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.
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
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
It only runs if there is "Yes"
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
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.
 
Joined
Feb 21, 2018
Messages
209
Reaction score
83
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.
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
I am going to start a new post. I think this one is too confusing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top