On 15 Aug 2005 08:01:24 -0700, "tiger" wrote:

Hello Ron,
thanks for the reply. I need the number to pop up into the same cell.
This would be ideal.
thanks,
tyra


Here's one way.

Set up your worksheet as follows:

======================
I would suggest using Data Validation with List for the Validation Criteria.

Set up your list in some range of cells; for example I1:I7

Insert/Name/Define: Responses (Refers to: $I$1:$I$7) or where ever you put
the list of responses.

I1: Strongly agree
I2: Agree
..
..
I7: Strongly disagree.

Then select, for example, A1:A20 as the range in which you will place the
responses.
From the main menu; select

Data/Validation
Settings
Allow: List
Source: $I$1:$I$7

Check the Ignore Blank and in-cell dropdown boxes.
======================

Now right click on the sheet tab and select View Code from the right click
menu.

Paste the code below into the window that opens:

=========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Set aoi = Range("A1:A20")
Application.EnableEvents = False

If Not Intersect(Target, aoi) Is Nothing Then
If IsNumeric(Target) Then GoTo Done
Target.Value = Application.WorksheetFunction. _
Match(Target.Text, Range("Responses"), 0)
End If

Done: Application.EnableEvents = True
End Sub
===========================

Hopefully, this will give you enough of a start to be able to modify it to your
precise requirements.

Post back if you have any questions.


--ron