Thanks for you ideas - I'm going to try them. Sorry for not saying that I'm
using Excel 2003, and I'm using Data Validation Drop Downs - settings: Allow
List, Source = ExpType, (a range name on a wksheet named Codes), Warning
after invalid data is entered "Please enter Exp Type before entering data."

"Jim Cone" wrote:

You don't say what type of dropdown you use and you didn't
mention the xl version. If you had said xl2007, I would have
ignored your post and you would be one of the lucky ones this time. <g

Some ideas (some not that good)...
1. Use dropdowns (comboboxes) from the "Control Tool Box".
They have a "ComboBox_Change" event that is separate from the
Worksheet_Change event.
2. Place all of your linked cells in the same column and check that
the change occurs in that column. If not, exit code.
3. Use Data Validation lists in the appropriate cells -
no change event necessary.
4. Lock the cells, so the user can't change them.
In your code, unlock the cells, do things, lock the cells.
(the linked cells, must remain unlocked)
--
Jim Cone
Portland, Oregon USA



"Dana M"

wrote in message
Note - current code is at the bottom of this question. I'm not sure, but
think this is called a worksheet event?
In columns C and D of several sheets in a template, I have drop down boxes
for selecting Type and Owner. If the user selects a Type Name, a Type
Abbreviation is returned (from a hidden "Code" sheet), ie; select Apple,
template shows AP, select Jack Web, template returns JW. The Type and Owner
drop downs are adjacent, in Columns C and D, but are not in every row
because of subtotals and blank rows.

My problem - users sometimes key in "AP" instead of selecting
"Apple", or copy/paste "AP" from another row. These actions are causing
errors.

Is there a way to either revise the following code so that copy/paste or
keying in an "AP" gives the same result
as selecting Apple from the Drop Down? Or alternately, not allow the user to
do anything but select - with a message if they attempt - that they need to
select from the drop down options? Here is my
current code - on right click of the worksheet tab/show code /
.........................
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 3 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("a1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ExpType"), 0), 0)
End If
Application.EnableEvents = True
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 4 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("d1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ExpOwner"), 0), 0)
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If
End Sub