Lists Problem

T

Tom

I want to create a list that is available to a cell in another worksheet.
I have two columns on an active worksheet, one column is to select an
individuals name and the other to select standard text. The list of
individuals and standard text are from a main worksheet common to more than
one worksheet. How is this accomplished?
 
L

Larry

Hi Debra,
I have been using and learning from the contextures site; aparently not fast
or indepth enough to solve for this issue. I would greatly appreciate your
help on the
DataValComboCheck code.
It has two pages that I have tweaked verylittle to my needs, one is a list
sheet "Lists" the other is a Data validation sheet, "NameList" both seem to
be sorting my data and I do not want it to do that. I need the list to be
dynamic and update as entries are made, but not sorted, this way I can start
the drop down list with all of our known issues in a descrepency list that
the user can choose to use or input their own descrepency data, but it should
remain in the last cell location.
Is there a way to change the sort Ascending command or take it out and still
have the dynamic funcionalty.
NameList code in the input 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("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
Lists code in the 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


I thought I cold just take this out: Order1:=xlAscending, _
but that doesn't work, nothing I have tried works.

I am hopeful that you can help me with this. thank You, larry
 
D

Debra Dalgleish

Hi Larry,

That looks like the DataValListAddSort.xls sample file. You could remove
the code from the Lists worksheet module, and the list will stop sorting
automatically.

There's a dynamic range (NameList) defined for that list,
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)
so as long as there are no blanks left in the range, it should work
correctly without sorting.

Debra
 
Top