hello,
I'm using Validation list in my sheet, and in case I've got a new item not in the list, the below code helping me to just type it and it will automatically added to the validation list,
but i face 2 problems which is :
First: I want the new item to be added in a new row, not just a new cell in the column.
Second : I use 2 worksheets for validation lists which (List - Subs) and I want to effect only Subs to add a new row, "List" is working fine by adding a new cell only
here is the code
Option Explicit
'add name to a list if its not in the data validation's list
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set ws = Worksheets("list")
Set ws = Worksheets("subs")
If Target.Row > 1 Then
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then Exit Sub
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
On Error Resume Next
Set rng = ws.Range(str)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
If Application.WorksheetFunction _
.CountIf(rng, Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
ws.Cells(i, rng.Column).Value = Target.Value
rng.Sort Key1:=ws.Cells(1, rng.Column), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
thank you in advance
Bookmarks