Win 2000 Pro; Office 2003

I have a form where the user can enter either an "Equipment code" or an "ECI
Number". Since we do not know which one the user will have at the time he
completes the form, we have constructed a table that allows translation from
whichever one they enter to the one they don't have:

Col1: Col2: Col3:
Equip Code ECI Number Equip Code

I have a "Change event for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'On Error GoTo xitsub
Application.EnableEvents = False

Select Case Target.Address
Case Is = "$E$49"
'Entered an Equipment Code
Cells(50, "E").Formula = "=VLOOKUP(E49,Y2:Z54,2,FALSE)"

Case Is = "$E$50"
'Entered an ECI Number
Range("E49").Formula = "=VLOOKUP(E50,Z2:AA54,2,FALSE)"

Case Else
End Select

xitsub:
Application.EnableEvents = True
End Sub

This works fine except that the cell that has the formula inserted displays
the Formula as text rather than evaluating the formula to its appropriate
result.

I have the cell formats set as text because both values have leading zeros I
need to display.

I have tried the Range().FormulaRC format but that did the same thing.

What am I missing here?

Thanx

BAC

The cell does not have to be formatted as text to display leading zeros
- try this:

enter '01234 in cell A1. Make sure that B1 is formatted as general,
then enter =A1.

Perhaps you can make use of this.

Pete

Just a try:

set the formats before putting the equation in
Range("E49").NumberFormat = "General", etc.
--
Gary's Student


"BAC" wrote:

Win 2000 Pro; Office 2003

I have a form where the user can enter either an "Equipment code" or an "ECI
Number". Since we do not know which one the user will have at the time he
completes the form, we have constructed a table that allows translation from
whichever one they enter to the one they don't have:

Col1: Col2: Col3:
Equip Code ECI Number Equip Code

I have a "Change event for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'On Error GoTo xitsub
Application.EnableEvents = False

Select Case Target.Address
Case Is = "$E$49"
'Entered an Equipment Code
Cells(50, "E").Formula = "=VLOOKUP(E49,Y2:Z54,2,FALSE)"

Case Is = "$E$50"
'Entered an ECI Number
Range("E49").Formula = "=VLOOKUP(E50,Z2:AA54,2,FALSE)"

Case Else
End Select

xitsub:
Application.EnableEvents = True
End Sub

This works fine except that the cell that has the formula inserted displays
the Formula as text rather than evaluating the formula to its appropriate
result.

I have the cell formats set as text because both values have leading zeros I
need to display.

I have tried the Range().FormulaRC format but that did the same thing.

What am I missing here?

Thanx

BAC