No typing in list validation cell
"Gord Dibben" wrote:
Is this something you could use?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A1"))
n = Target.Row
If vRngInput Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng In vRngInput
Select Case rng.Value
Case Is = "Cabbage": Text = "Green"
Case Is = "Squash": Text = "Yellow"
Case Is = "Egg Plant": Text = "Black"
Case Is = "Tomato": Text = "Red"
End Select
Excel.Range("A" & n).Value = Text
Next rng
Application.EnableEvents = True
End Sub
Gord
On Sun, 30 Sep 2007 20:29:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:
See in-line..........
On Sun, 30 Sep 2007 20:06:01 -0700, Kenny
wrote:
Thanks for your help Gord. But I do not want user to be able to enter text
into the cell with the validation list, problrm is wether the check box is
clicked or not. You can type anything into the cell you want to.
This is not true.........if "Show error alert" is checked, users can enter only
from the list and cannot enter anything that is not on the list.
As for the
other problem, everyone tells me to refer to that site, but my problem is not
shown there. I am not wanting multiple list drop downs. I just want to list
column a and select the choice, but it puts the column b in the cell?
You cannot have another cell value be placed into the DV cell when you click on
the DV dropdown and pick an item unless you were to use event code.
I think you should look at using VLOOKUP in a secondary cell to return the value
from the other sheet..
For event code see this example where you would select a number from a DV
dropdown list in A1 and return a letter grade to A1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A1"))
n = Target.Row
If vRngInput Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the range
Select Case rng.Value
Case Is < 20: Text = "F"
Case 20 To 35: Text = "E"
Case 36 To 50: Text = "D"
Case 50 To 65: Text = "C"
Case 66 To 85: Text = "B"
Case Is 85: Text = "A"
End Select
'Apply the Letter Grade
Excel.Range("A" & n).Value = Text
Next rng
Application.EnableEvents = True
End Sub
Gord
|