Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to add new items to a list
I have created a drop down box in page 1 of a spreadsheet. The list it refers
to is in page 2 of the same workbook. I want users to be able to pick form the already defined list from the drop down box but in case where there is something new not on the list, I need users to be able to free type into the cell. Any ideas how to do this? thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to add new items to a list
Instruct your users to disable the data validation to enter free text.
-- Gary''s Student - gsnu200783 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to add new items to a list
On May 2, 8:57 pm, peter pan <peter
wrote: I have created a drop down box in page 1 of a spreadsheet. The list it refers to is in page 2 of the same workbook. I want users to be able to pick form the already defined list from the drop down box but in case where there is something new not on the list, I need users to be able to free type into the cell. Any ideas how to do this? thanks Deselect the "Show error alert after invalid data is entered" option on the "Error Alert" tab of the "Data Validation" dialog, then unlisted entries are accepted. If you want the new entries added to the drop down list then use a Worksheet_Change event procedure to do that. For example, say the list is a dynamic named range named MyList and the data is entered in D1, then something like... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D1")) Is Nothing Then On Error GoTo ERROR_HANDLER If WorksheetFunction.CountIf(Range("MyList"), Target.Value) = 0 Then Application.EnableEvents = False Range("MyList").Offset(Range("MyList").Cells.Count , 0) _ .Cells(1).Value = Target.Value Application.EnableEvents = True End If End If Exit Sub ERROR_HANDLER: Application.EnableEvents = True End Sub Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching items in 2 list to consolidate to one list | Excel Discussion (Misc queries) | |||
last 20 items in a list | Excel Worksheet Functions | |||
How do I delete items in one list from another list? | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
sum particular items from a list | Excel Discussion (Misc queries) |