software

Win 2000 Pro; Office 2003

I have a form where the user can enter either an quot;Equipment codequot; or an quot;ECI
Numberquot;. 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 quot;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 = quot;$E$49quot;
'Entered an Equipment Code
Cells(50, quot;Equot;).Formula = quot;=VLOOKUP(E49,Y2:Z54,2,FALSE)quot;

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

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.

PeteJust a try:

set the formats before putting the equation in
Range(quot;E49quot;).NumberFormat = quot;Generalquot;, etc.
--
Gary's Studentquot;BACquot; wrote:

gt; Win 2000 Pro; Office 2003
gt;
gt; I have a form where the user can enter either an quot;Equipment codequot; or an quot;ECI
gt; Numberquot;. Since we do not know which one the user will have at the time he
gt; completes the form, we have constructed a table that allows translation from
gt; whichever one they enter to the one they don't have:
gt;
gt; Col1: Col2: Col3:
gt; Equip Code ECI Number Equip Code
gt;
gt; I have a quot;Change event for the worksheet:
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; 'On Error GoTo xitsub
gt; Application.EnableEvents = False
gt;
gt; Select Case Target.Address
gt; Case Is = quot;$E$49quot;
gt; 'Entered an Equipment Code
gt; Cells(50, quot;Equot;).Formula = quot;=VLOOKUP(E49,Y2:Z54,2,FALSE)quot;
gt;
gt; Case Is = quot;$E$50quot;
gt; 'Entered an ECI Number
gt; Range(quot;E49quot;).Formula = quot;=VLOOKUP(E50,Z2:AA54,2,FALSE)quot;
gt;
gt; Case Else
gt; End Select
gt;
gt; xitsub:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; This works fine except that the cell that has the formula inserted displays
gt; the Formula as text rather than evaluating the formula to its appropriate
gt; result.
gt;
gt; I have the cell formats set as text because both values have leading zeros I
gt; need to display.
gt;
gt; I have tried the Range().FormulaRC format but that did the same thing.
gt;
gt; What am I missing here?
gt;
gt; Thanx
gt;
gt; BAC
gt;
gt;
gt;