No typing in list validation cell

  • Thread starter Thread starter Guest
  • Start date Start date
By default a DV list cell will accept only what is in the list.

Typing anything not on the list will bring up an error message.

Make sure you have checkmarked Error Alert>Show error alert after invalid data
is entered .


Gord Dibben MS Excel MVP
 
i have the list validation applied to the cell, and error alert is checked. I
can select from the list and it populates the cell with that info, or I can
just ype anything I want to in the cell, even if it is not on the list and it
allows this?

Can you help me with this other problem also?
maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!
 
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. 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?
 
See in-line..........

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.
Click to expand...

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?
Click to expand...

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
 
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
 
Gord Dibben said:
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
Click to expand...
 
Well thanks, but no.

I want to use the list VALIDATION and use the data from sheet2. I want to be
able to add more data to sheet 2 as needed. I want to click on the arrow and
it populate the validation list with column a from sheet 2. Once I make a
selection I want it to populate that same cell, not with the choice from
column A, but the corresponding cell in column b. Is this even possible?
 
Back
Top