Additional Programming

[Guest]

I have a form that when I enter an employee # it automatically changes the #
to the employee's name. I'd like to use this same code a number of other
times on this form for other applications that have nothing to do with the
employee and #. Can I have more then 1 piece of code on a form??
Thanks
Jon

My current code is:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 8 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets(".").Range("F1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets(".").Range("Employee_ID"), 0), 0)
Application.EnableEvents = True
End If
End Sub

 

[Dave Peterson]

You may be able to modify this to what you want.

I assumed that you had more range names on the worksheet named "."

You're going to have to use some indication of what rangename you want to look
through. I chose to use the column where you're entering the data.

I put 3 range names on worksheet(".")
Employee_ID, OtherTable_1, and OtherTable_2.

I wanted to enter employee_id stuff in column 8, othertable_1 stuff in column 9,
and othertable_2 stuff in column 10.

And I wanted to bring back certain columns (2nd column from employee_id, 4th
from othertable_1, and 3rd from othertable_2.

This is the portion of the code you'll have to modify for your situation:

Select Case Target.Column
Case Is = 8
myLookUpRngName = "Employee_ID"
myColToBringBack = 2
Case Is = 9
myLookUpRngName = "OtherTable_1"
myColToBringBack = 4
Case Is = 10
myLookUpRngName = "OtherTable_2"
myColToBringBack = 3
End Select

(If you want to bring back column 10 the employee id table, make sure that there
are at least 10 columns in that range. I didn't build any validity check in
(you may want to!).


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myLookUpRngName As String
Dim myLookUpRng As Range
Dim myColToBringBack As Long

Dim res As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

myLookUpRngName = ""
Select Case Target.Column
Case Is = 8
myLookUpRngName = "Employee_ID"
myColToBringBack = 2
Case Is = 9
myLookUpRngName = "OtherTable_1"
myColToBringBack = 4
Case Is = 10
myLookUpRngName = "OtherTable_2"
myColToBringBack = 3
End Select

If myLookUpRngName = "" Then Exit Sub

Set myLookUpRng = Nothing
On Error Resume Next
Set myLookUpRng = Worksheets(".").Range(myLookUpRngName)
On Error GoTo 0

If myLookUpRng Is Nothing Then
MsgBox "Design error--missing: " & myLookUpRngName
Exit Sub
End If

res = Application.Match(Target.Value, myLookUpRng.Columns(1), 0)

If IsError(res) Then
'do nothing--no match in the table!
Else
Application.EnableEvents = False
Target.Value = myLookUpRng(res, myColToBringBack).Value
Application.EnableEvents = True
End If
End Sub