Adding data to dropdown list?

  • Thread starter Thread starter biben
  • Start date Start date
B

biben

Hi
I want a dropdown list to work like this:

I start to write in the cell and if the value is in the Name-list i
will open the list to choose from, if else let me type in a new valu
which is added to my name-list and stored.

Someone?

/bibe
 
Create a Category List
--Type your list of categories
--Select the cells in the list
--Click in the Name box, to the left of the formula bar
--Type a one-word name for the list, e.g. Categories.
--Press the Enter key.

Apply Data Validation
--Select the cells in which the user will select or type a category.
--Choose Data>Validation
--From the Allow dropdown, choose List
--In the Source box, type: =Categories
--On the Error Alert tab, remove the check mark from 'Show error alert
after invalid data is entered
--Click OK

Add code to append new categories
--Right-click the sheet tab on the sheet with data validation
--Choose View Code, and paste in the following code
(In this example, the Categories list is on Sheet1, and the
Data Validation is on Sheet2, Column A)

Users can select or type a category. New categories will be
automatically added to the categories list.

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet
Dim rng As Range
Set ws1 = Worksheets("Sheet1")
Set rng = ws1.Range("Categories")
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 And Target.Row > 1 Then
If Application.WorksheetFunction _
.CountIf(rng, Target.Value) Then
'do nothing
Else
rng.Offset(rng.Cells.Count, 0) _
.Resize(1, 1).Value = Target.Value
rng.CurrentRegion.Name = "Categories"
Set rng = ws1.Range("Categories")
rng.Sort Key1:=ws1.Range("A1"), _
Order1:=xlAscending, Header:=xlNo
End If
End If
End Sub
'=================================
 
Back
Top