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