Data Validation

[Connie Martin]

In data validation I have selected "list" under "Allow" and then put the
Source in my dragging down on the list I have created at the bottom of the
spreadsheet. The list is this:
D = Delivery - delivery too long
P = Price
CC = Customer changed requirements
OE = Order error, customer
SDH = Shutdown - Holiday
SDM = Shutdown - Mechanical
SDO - Shutdown - Other

My question is this: is there a way that when any one of them is selected
that it puts in only the code, i.e. D, P, CC, OE, SDH, etc.? Connie

 

[Dave Peterson]

Debra Dalgleish shows a way:
http://contextures.com/excelfiles.html#DataVal

Look for:
DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.
DataValCode.zip 8 kb

 

[Connie Martin]

Thank you for responding. I have tried to adapt this example, following it
to the letter, as far as I can see, but when I select from the dropdown list,
it opens the code with a pop-up which says, "Compile error: Ambiguous name
detected: Worksheet_Change". I've looked at Help on this but it's Greek to
me. I have another macro in this workbook, so I don't know if there's some
sort of conflict, or what. Can you give me any idea? Thank you. Connie

 

[Dave Peterson]

You get one worksheet_change event per worksheet. If you want to do different
things based on different changes, then you'll have to merge your code into a
single procedure.

Without knowing what you're doing, the "basic" code could be as simple as:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Me.Range("A1")) Is Nothing) Then
'do the stuff for the change to A1
Else
If Not (Intersect(Target, Me.Range("q7:z99")) Is Nothing) Then
'do the stuff for q7:z99
End If
End If
End Sub

 

[Connie Martin]

You've lost me! Sorry! The other worksheet change I have in this worksheet
is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not (Application.Intersect(Target, Range("A2:A5000,C2:C5000,J2:J5000"))
Is Nothing) _
Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End Sub

 

[Connie Martin]

Dave, never mind! I don't really need that other worksheet change. I
deleted it and this one you sent me works great. Thank you. Connie

 

[Dave Peterson]

Well, that makes it easy <vbg>.

Dave, never mind! I don't really need that other worksheet change. I
deleted it and this one you sent me works great. Thank you. Connie