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

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

"Connie" skrev i meddelelsen
...
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

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 wrote:

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

--

Dave Peterson

On Oct 12, 1:53*pm, Dave Peterson wrote:
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 wrote:

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

--

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.