how to add new items to a list

  • Thread starter Thread starter peter pan
  • Start date Start date
P

peter pan

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
 
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
Click to expand...

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
 
Back
Top