I have this line in a workbook that I am using a dynamic list in. I want the
list to remain dynamic but do not want it to sort the input data. How can I
write this so it will not sort ascending or descending, just leavve it in the
cell entered? thanks

Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _


Full code:
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

Larry,
Simply delete all the code OR comment it out in case you need
to revert to sorting later.

To comment out, highlight the code and click the "Comment Block" icon on the
EDit Toolbar in VBE


"Larry" wrote:

I have this line in a workbook that I am using a dynamic list in. I want the
list to remain dynamic but do not want it to sort the input data. How can I
write this so it will not sort ascending or descending, just leavve it in the
cell entered? thanks

Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _


Full code:
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

Hey thanks, it sort of works, that is, it stops sorting but it will not add
new items to the list. I should have put in the code from the input (data
validation) sheet:


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("Na meList"),
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
It also has a sort command.
thanks topper!


"Toppers" wrote:

Larry,
Simply delete all the code OR comment it out in case you need
to revert to sorting later.

To comment out, highlight the code and click the "Comment Block" icon on the
EDit Toolbar in VBE


"Larry" wrote:

I have this line in a workbook that I am using a dynamic list in. I want the
list to remain dynamic but do not want it to sort the input data. How can I
write this so it will not sort ascending or descending, just leavve it in the
cell entered? thanks

Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _


Full code:
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

This code (in "Input") worked for me. I was confused (in your earlier
posting) by the fact the sort appeared to be called twice (from "Lists" and
"Input")

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim i As Integer

On Error GoTo wsexit
Application.EnableEvents = False

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value '
End If
End If

wsexit:
Application.EnableEvents = True

End Sub

HTH


"Larry" wrote:

Hey thanks, it sort of works, that is, it stops sorting but it will not add
new items to the list. I should have put in the code from the input (data
validation) sheet:


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("Na meList"),
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
It also has a sort command.
thanks topper!


"Toppers" wrote:

Larry,
Simply delete all the code OR comment it out in case you need
to revert to sorting later.

To comment out, highlight the code and click the "Comment Block" icon on the
EDit Toolbar in VBE


"Larry" wrote:

I have this line in a workbook that I am using a dynamic list in. I want the
list to remain dynamic but do not want it to sort the input data. How can I
write this so it will not sort ascending or descending, just leavve it in the
cell entered? thanks

Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _


Full code:
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

Hey Toppers,
I retrieved this code from the contextures site: DataValComboCheck. I took
the code and just added a validation list to the list sheet. So this code is
as I got it and it works great if one does not mind having everything
re-sorted each time somthing is added.
I have tried a few things but I'm a rookie and haven't had much luck. I wish
to hae this set up so the list is still dynamic but not sorting. I noted the
contextures article about dynamic ranges and the same string of code is used
in the defined name source of this sample workbook for the input list called
"NamedList".
any helpful ideas to get a dynamic list that will take new additions but not
sort them ascending or otherwise? Thanks so much for being there Toppers.
larry

"Toppers" wrote:

This code (in "Input") worked for me. I was confused (in your earlier
posting) by the fact the sort appeared to be called twice (from "Lists" and
"Input")

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim i As Integer

On Error GoTo wsexit
Application.EnableEvents = False

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value '
End If
End If

wsexit:
Application.EnableEvents = True

End Sub

HTH


"Larry" wrote:

Hey thanks, it sort of works, that is, it stops sorting but it will not add
new items to the list. I should have put in the code from the input (data
validation) sheet:


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("Na meList"),
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
It also has a sort command.
thanks topper!


"Toppers" wrote:

Larry,
Simply delete all the code OR comment it out in case you need
to revert to sorting later.

To comment out, highlight the code and click the "Comment Block" icon on the
EDit Toolbar in VBE


"Larry" wrote:

I have this line in a workbook that I am using a dynamic list in. I want the
list to remain dynamic but do not want it to sort the input data. How can I
write this so it will not sort ascending or descending, just leavve it in the
cell entered? thanks

Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _


Full code:
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

Larry,
It works OK for me.

I had "Namelist" in column A of w/sheet "Lists" defined as a dynamic list
[=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)]. I initialised it with 5
values and then entered "Namelist" (no quotes) in the Name Box (which to the
left of the function box) and hit enter. This highlighted cells A1 to A5.

In w/sheet "Input" (which has the code attached) I entered a value in column
C and "Namelist" was updated i.e. A6 was filled. I then entered "Namelist" in
the Name Box, hit enter, and cells A1 to A6 were highlighted; I repeated this
and cells A1 to A7 were highlighted so the list is dynamic.

Equally, if I simply add data to column A in "Lists" the correct cells are
highlighted.
Why do you think it isn't?

HTH

"Larry" wrote:

Hey Toppers,
I retrieved this code from the contextures site: DataValComboCheck. I took
the code and just added a validation list to the list sheet. So this code is
as I got it and it works great if one does not mind having everything
re-sorted each time somthing is added.
I have tried a few things but I'm a rookie and haven't had much luck. I wish
to hae this set up so the list is still dynamic but not sorting. I noted the
contextures article about dynamic ranges and the same string of code is used
in the defined name source of this sample workbook for the input list called
"NamedList".
any helpful ideas to get a dynamic list that will take new additions but not
sort them ascending or otherwise? Thanks so much for being there Toppers.
larry

"Toppers" wrote:

This code (in "Input") worked for me. I was confused (in your earlier
posting) by the fact the sort appeared to be called twice (from "Lists" and
"Input")

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim i As Integer

On Error GoTo wsexit
Application.EnableEvents = False

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value '
End If
End If

wsexit:
Application.EnableEvents = True

End Sub

HTH


"Larry" wrote:

Hey thanks, it sort of works, that is, it stops sorting but it will not add
new items to the list. I should have put in the code from the input (data
validation) sheet:


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("Na meList"),
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
It also has a sort command.
thanks topper!


"Toppers" wrote:

Larry,
Simply delete all the code OR comment it out in case you need
to revert to sorting later.

To comment out, highlight the code and click the "Comment Block" icon on the
EDit Toolbar in VBE


"Larry" wrote:

I have this line in a workbook that I am using a dynamic list in. I want the
list to remain dynamic but do not want it to sort the input data. How can I
write this so it will not sort ascending or descending, just leavve it in the
cell entered? thanks

Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _


Full code:
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