Let's say you have to add codes like this all day:

8 - 5 k
8-5 d
5-cl p
12-10 s

8 - 5 k means that the employee worked from 8 to 5 in the kitchen, etc.

Probably you would find it tedious and mistakes are bound to happen.

Imagine you could type in 1, 2, 3, 4 instead. When you type 1, 8 - 5 k appears, when you type 2, 8-5 d, etc. That would save time and improve accurracy.

I created a worksheet called "tabla" with a range called tabla, item, valor, and max.

The range tabla has two columns. First columns is for the codes 1, 2, 3, ... and the second column is for the values 8 - 5 k, etc.

Item is a single cell with no formulae.

Valor is a single cell with the formulae =VLOOKUP(item;tabla;2;FALSE).

Max is a single cell with the formulae =MAX(A2:A16).

This is my code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "tabla" Then
If Target.Value > 0 And Target.Value <= Range("max") Then
Range("Item") = Target.Value
Cells(Target.Row, Target.Column) = Range("valor")
End If
End If
End Sub

Every time you type 1, 2, 3, ... it is replaced by the corresponding code.

Note that the user does not have to change the code. All he has to do is to change the values in the range tabla.

I am sure this can be done in many other ways, but I found this one neat.

My major problem in writing the code was to realise that I had to test if the sheet name was "tabla".