Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and Multi Lists | New Users to Excel | |||
generate a random number and use if function to generate new data | Excel Worksheet Functions | |||
Excel - need a function to compare lists | Excel Worksheet Functions | |||
How to generate a report from multiple lists in Excel 2003? | Excel Discussion (Misc queries) |