Another question on Auto Filling Cells

J

J Powers

Is there a way to program a cell so that if you just enter something like an
employee ID # it will automatically change it to the official name needed on
the report??

IE I enter Employee # rp207 it will show James Doe

Thanks in advance
J Powers
 
D

Dave Peterson

You could use an input cell and a helper cell to return what you want.

I'd build a list of employee id's on a separate sheet (in column A). Then put
the names in column B (and more useful stuff in C:X????).

Then you could use =vlookup() to return the information you want.

If you can live with this idea, take a look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html

She explains =vlookup() with nice pictures and examples.
 
J

J Powers

Thank you for the info it is very helpful! Is there anyway to do it all in
the same cell?

Thanks
J Powers
 
J

J Powers

This is Exactly what I am looking for but I am pretty new to these advanced
features. Can anyone please take a look at the programing and explain to me
what it means and how I would create the change event for a cell? I copy and
past it into my worksheet and change what I think needs to be changed but it
still doesn't seem to work. Can anyone help???

Thanks
Jon
 
D

Debra Dalgleish

The code should be stored on a worksheet module. There are instructions
here:

http://www.contextures.com/xlvba01.html#Worksheet

In the sample file, the product names are on a sheet named "Codes", and
the list of products is named "ProdList". Change those references to
match your names.

The code runs when there is a change in column 2 (Target.Column = 2).
Change that if your data validation cells are in a different column.
 
D

Dave Peterson

First, what version of excel are you using?

If you're using xl97, then you may have trouble:

This is from Debra Dalgleish's site:
http://www.contextures.com/xlDataVal08.html

In Excel 97, selecting an item from a Data Validation dropdown list does
not trigger a Change event, unless the list items have been typed in the
Data Validation dialog box.


So if you're using xl97 and data|validation with a list placed on a worksheet,
then you're going to have to do something else.

But if you're not using xl97 (or your list is typed into that data|validation
dialog directly), then it'll work ok.

First, Deb set up her "translation codes" worksheet on a worksheet named Codes.

She put the key data in column B and the value to be returned in column A. (She
didn't use =vlookup().)

You'll have to create a range name (Debra used ProdList) that points at
B2:B(lastrow).

Then rightclick on the worksheet tab that is going to be used as data entry.
Select view code and paste her code in that code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
Application.EnableEvents = True
End If
End Sub

After you paste this in, you'll want to modify it to match what you did. If you
used a different worksheet names (not "Codes"), then change it here.

If you used a different range name, then change it here.

======
If you are using xl97 and have to use a list on a different worksheet, you can
accomplish the same sort of thing without using Data|Validation.

You'll still need to build that list for translation--and I used Deb's
layout--key in B, value to be returned in A. (And the B2:b(lastrow) named
Prodlist.)

Rightclick on the worksheet tab of the data input worksheet and select view
code. Paste this in. (Don't put Deb's code and this code in at the same
time--choose the one you want and delete the other.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim res As Variant
Dim myValidationRng As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b2:b65536")) Is Nothing Then Exit Sub

If Target.Value = "" Then Exit Sub
Set myValidationRng = Worksheets("codes").Range("prodlist")

On Error GoTo errHandler:

res = Application.Match(Target.Value, myValidationRng, 0)

Application.EnableEvents = False
If IsError(res) Then
Application.Undo
MsgBox "Invalid entry"
Else
'cell to the left (col A)
Target.Value = myValidationRng(res).Offset(0, -1)
End If

errHandler:
Application.EnableEvents = True

End Sub
 
J

J Powers

Thank you Both Very Much!! This works better then I could have wanted.
Thank you for taking the time to explain it and assist!

Jon
 
Top