Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
nelli
 
Posts: n/a
Default How do I generate Errors for Drop-Down Lists?

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I generate Errors for Drop-Down Lists?

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
nelli
 
Posts: n/a
Default How do I generate Errors for Drop-Down Lists?

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I generate Errors for Drop-Down Lists?

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
nelli
 
Posts: n/a
Default How do I generate Errors for Drop-Down Lists?

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!




  #6   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I generate Errors for Drop-Down Lists?

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP and Multi Lists dpatte601 New Users to Excel 6 May 17th 06 10:40 AM
generate a random number and use if function to generate new data Dogdoc1142 Excel Worksheet Functions 4 April 26th 06 03:44 AM
Excel - need a function to compare lists JerryMatson Excel Worksheet Functions 1 November 24th 05 05:09 PM
How to generate a report from multiple lists in Excel 2003? Mobile PhD Excel Discussion (Misc queries) 0 July 26th 05 10:22 PM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"