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

how to add new items to a list
 

Instruct your users to disable the data validation to enter free text.
--
Gary''s Student - gsnu200783

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