Using a named range in the Worksheet_Change event

[Connie]

I have a sheet named "Lists". Cells A3..A11 contain a range which
I've named Technician_Codes. Cells A15..A25 contain a range which
I've named Support_Codes. I am using the Worksheet_Change event to
validate user entry on a sheet named "Field Rep Time Sheet". I am
using the Worksheet_Change event to validate the entry as I have
several validations to do, and the logic is too complicated for the
Data Validation tool.

The user will be entering data in cells g9..15, and the same
validation applies for each cell. In the code below, how do I avoid
hardcoding g9? I want to test the user's entry one by one as input is
made into cells g9..g15. Also, is there any way I can avoid
hardcoding the ranges -- how do I use the range names
(Technician_Codes and Support_Codes) that I've created? This is only
part of the validation that I am doing; however, if I can get through
this, I should be able to figure out the rest.

Thanks.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vRange As Range
Dim TempTechNo As Integer
Dim sh As Worksheet

Set vRange = Range("g9")
TempTechNo = Range("tech_no").Value

Set sh = Worksheets("Lists")

'Set range based on whether employee is technician or hourly
If TempTechNo > 0 Then
Set rng = sh.Range("$A$3:$A$11")
Else
Set rng = sh.Range("$A$15:$A$25")
End If

'Lookup user entry in correct list to confirm that it is valid.

ReturnValue = Application.VLookup(vRange, _
sh.Range(rng.Address), 1, False)
If IsError(ReturnValue) Then
strMsg = "The code you entered is incorrect. Try again."
MsgBox strMsg, vbOKOnly + vbInformation, "Invalid Code"
End If

End Sub

 

[Per Jessen]

Hi

The Target variable is returning the cell which has been changed, so we test
if Target is intersecting with G9:G15.

Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim isect As Variant
Dim vRange As Range
Dim TempTechNo As Integer
Dim sh As Worksheet
Set isect = Intersect(Target, Range("G9:G15"))
If Not isect Is Nothing Then
Set vRange = Target
TempTechNo = Range("tech_no").Value

Set sh = Worksheets("Lists")

'Set range based on whether employee is technician or hourly
If TempTechNo > 0 Then
Set rng = sh.Range("$A$3:$A$11")
Else
Set rng = sh.Range("$A$15:$A$25")
End If

'Lookup user entry in correct list to confirm that it is valid.

ReturnValue = Application.VLookup(vRange, _
sh.Range(rng.Address), 1, False)
If IsError(ReturnValue) Then
strMsg = "The code you entered is incorrect. Try again."
MsgBox strMsg, vbOKOnly + vbInformation, "Invalid Code"
End If
End If
End Sub

Regards,
Per

 

[Dave Peterson]

Since you're only looking to see if a value is in the list, I'd use
application.match instead of application.vlookup.

And sh.range(rng.address) is overkill if rng is on sh. Just using rng is
sufficient.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vRange As Range
Dim TempTechNo As Integer
Dim sh As Worksheet
Dim rng As Range
Dim ReturnValue As Variant 'could be an error
Dim strMsg As String

Set vRange = Me.Range("g9:g15")

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, vRange) Is Nothing Then
Exit Sub
End If

TempTechNo = Me.Range("tech_no").Value

Set sh = Worksheets("Lists")

'Set range based on whether employee is technician or hourly
If TempTechNo > 0 Then
Set rng = sh.Range("$A$3:$A$11")
Else
Set rng = sh.Range("$A$15:$A$25")
End If

'Lookup user entry in correct list to confirm that it is valid.
ReturnValue = Application.Match(Target.Value, rng, 0)
If IsError(ReturnValue) Then
strMsg = "The code you entered is incorrect. Try again."
MsgBox strMsg, vbOKOnly + vbInformation, "Invalid Code"
End If

End Sub

 

[Connie]

Since you're only looking to see if a value is in the list, I'd use
application.match instead of application.vlookup.

And sh.range(rng.address) is overkill if rng is on sh.  Just using rng is
sufficient.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim vRange As Range
    Dim TempTechNo As Integer
    Dim sh As Worksheet
    Dim rng As Range
    Dim ReturnValue As Variant 'could be an error
    Dim strMsg As String

    Set vRange = Me.Range("g9:g15")

    If Target.Cells.Count > 1 Then
        Exit Sub 'one cell at a time
    End If

    If Intersect(Target, vRange) Is Nothing Then
        Exit Sub
    End If

    TempTechNo = Me.Range("tech_no").Value

    Set sh = Worksheets("Lists")

    'Set range based on whether employee is technician or hourly
    If TempTechNo > 0 Then
        Set rng = sh.Range("$A$3:$A$11")
    Else
        Set rng = sh.Range("$A$15:$A$25")
    End If

    'Lookup user entry in correct list to confirm that it is valid.
    ReturnValue = Application.Match(Target.Value, rng, 0)
    If IsError(ReturnValue) Then
        strMsg = "The code you entered is incorrect.  Try again."
        MsgBox strMsg, vbOKOnly + vbInformation, "Invalid Code"
    End If

End Sub














--

Dave Peterson- Hide quoted text -

- Show quoted text -

These posts were very helpful. I believe I have the code working, but
I must test some more. Thanks so much for taking the time.