Help please - Data sorting

L

Larry

Hi all,
I would appreciate a bit of help with thsi. I got some code for a dynamic
list from Contextures and it works great except it wants to sort my list for
me which gums everything up.
I have a workbook with two sheets, one is Lists and one is Input

In the lists I want to have info that is set into categories i.e. "Front of
vehicle", Rear of vehicle. the input drop down will show this and the user
selectw what they want or adds a new item. I do not want the new item to be
pushed somewhere inside a sorted list, rather it should be added to the end
of the pre-determined list I have created.

the code is as follows:
Lists sheet:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Th Input (data validation)
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

Please help me figure out how to keep the dynamic functionality but lose the
sorting so my lists stays as I created it. Thanks, larry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top