How do I generate Errors for Drop-Down Lists?

[nelli]

I created a dropdown list with 20 different options.

If a value that doesn't exist is entered into the cell with the list, an
error message appears. This works perfect!

My problem is the folowing: If a value that doesn't exist in the list is
pasted into the cell that contains the list. The value is displayed in the
cell, and no error message occurs. How do I generate an error for this
scenario??

Please Help

[Dave Peterson]

It sounds like you used data|validation to create that dropdown list.

One way around this kind of problem is to put the validation into a worksheet
event or maybe even use a dropdown from the Forms toolbar or a combobox from the
control toolbox toolbar.



nelli wrote:
>
> I created a dropdown list with 20 different options.
>
> If a value that doesn't exist is entered into the cell with the list, an
> error message appears. This works perfect!
>
> My problem is the folowing: If a value that doesn't exist in the list is
> pasted into the cell that contains the list. The value is displayed in the
> cell, and no error message occurs. How do I generate an error for this
> scenario??
>
> Please Help

--

Dave Peterson

[nelli]

Yes I used data/validation to create the dropdown list.

I don't really understand what I should do.

is it possible that you can send me a link with an example on how to fix my
problem?

Thank you very much!

"Dave Peterson" wrote:

> It sounds like you used data|validation to create that dropdown list.
>
> One way around this kind of problem is to put the validation into a worksheet
> event or maybe even use a dropdown from the Forms toolbar or a combobox from the
> control toolbox toolbar.
>
>
>
> nelli wrote:
> >
> > I created a dropdown list with 20 different options.
> >
> > If a value that doesn't exist is entered into the cell with the list, an
> > error message appears. This works perfect!
> >
> > My problem is the folowing: If a value that doesn't exist in the list is
> > pasted into the cell that contains the list. The value is displayed in the
> > cell, and no error message occurs. How do I generate an error for this
> > scenario??
> >
> > Please Help
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Try this against a copy of your workbook--just in case.

Delete the data|validation from that cell
show the control toolbox toolbar
click on the combobox icon
Add a combobox that floats above that cell.

rightclick on the combobox and choose properties.

Scroll to style
change it to "2 - fmStyleDropDownList"
(This means that only the values that are in the dropdown can be used--the user
can't type a different value)

Scroll to linkedcell.
Link to the cell beneath the combobox (if you want)
Then your formulas that depend on that cell won't have to change.
(you can also link to an out of the way cell (on a hidden sheet) that the user
doesn't even know exists)

Scroll to ListFillRange:
type the range address that you used for the Data|Validation list
'sheet2'!a1:a10
(for example)

There a lots more options in that properties box that you may want to experiment
with:
colors and matchentry may give you some ideas

You can rightclick on the combobox and choose format control|properties to print
the combobox (or not).

Then when you're done experimenting, click on the Design mode icon (also on the
control toolbox toolbar) to tell excel that you're ready to behave as a
user--not developer.


nelli wrote:
>
> Yes I used data/validation to create the dropdown list.
>
> I don't really understand what I should do.
>
> is it possible that you can send me a link with an example on how to fix my
> problem?
>
> Thank you very much!
>
> "Dave Peterson" wrote:
>
> > It sounds like you used data|validation to create that dropdown list.
> >
> > One way around this kind of problem is to put the validation into a worksheet
> > event or maybe even use a dropdown from the Forms toolbar or a combobox from the
> > control toolbox toolbar.
> >
> >
> >
> > nelli wrote:
> > >
> > > I created a dropdown list with 20 different options.
> > >
> > > If a value that doesn't exist is entered into the cell with the list, an
> > > error message appears. This works perfect!
> > >
> > > My problem is the folowing: If a value that doesn't exist in the list is
> > > pasted into the cell that contains the list. The value is displayed in the
> > > cell, and no error message occurs. How do I generate an error for this
> > > scenario??
> > >
> > > Please Help
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[nelli]

Thanks Dave.

I tried using a combobox.
I have 2 problems:

1. I would like to be able to paste valid data into the cell.
2. I want to use the sam validation for more than 5000 cells, so creating a
combobox for each of them will take up too much time.

Do you maybe have another solution to my problem?

Thanks so much for your help!

[Dave Peterson]

Maybe use that worksheet_change event. But that doesn't give you the dropdown
to choose from.

I put a list of valid entries on Sheet99 in A1:A10 and gave it a name of MyList.

Then I rightclicked on the worksheet tab that needs this behavior and selected
view code. I pasted this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim res As Variant

On Error GoTo errHandler:

'only validate changes to a single cell
If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("b3:b5002")) Is Nothing Then
Exit Sub
End If

Set myRng = Worksheets("sheet99").Range("a1:a10")

res = Application.Match(Target.Value, myRng, 0)

If IsError(res) Then
With Application
.EnableEvents = False
.Undo
MsgBox "Invalid entry"
End With
End If

errHandler:
Application.EnableEvents = True

End Sub





nelli wrote:
>
> Thanks Dave.
>
> I tried using a combobox.
> I have 2 problems:
>
> 1. I would like to be able to paste valid data into the cell.
> 2. I want to use the sam validation for more than 5000 cells, so creating a
> combobox for each of them will take up too much time.
>
> Do you maybe have another solution to my problem?
>
> Thanks so much for your help!

--

Dave Peterson