How do I not allow typing in a cell that has a list validation?

Thanks!

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 AlertShow error alert after invalid data
is entered .


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 11:57:02 -0700, Kenny
wrote:

How do I not allow typing in a cell that has a list validation?

Thanks!

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!


"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much



"Gord Dibben" wrote:

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 AlertShow error alert after invalid data
is entered .


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 11:57:02 -0700, Kenny
wrote:

How do I not allow typing in a cell that has a list validation?

Thanks!

Uncheck "Show Error Alert etc." under the Error Alert Tab and you can type in
anything you want.

Your other problem may be resolved by a dependent list.

See Debra Dalgleish's site for more info.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 14:54:01 -0700, Kenny
wrote:

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?

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?


"Gord Dibben" wrote:

Uncheck "Show Error Alert etc." under the Error Alert Tab and you can type in
anything you want.

Your other problem may be resolved by a dependent list.

See Debra Dalgleish's site for more info.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 14:54:01 -0700, Kenny
wrote:

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?

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

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

"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

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?


"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