How to customize KBase sort routine?

[Gingertrees]

It looks like this code
http://www.vbaexpress.com/kb/getarticle.php?kb_id=479
is exactly what I need to sort my list alphabetically, but I don't know how to customize it to work with my project. I have an offset dynamic named range (1 column, starting with row 358). The name of that list is NAMES. (Some fake data is there for demonstration). So I understand I need to replace MyData with Names in this kbase code...but I do not understand how else to make it work. Especially the If Not Intersect part...it defined NMS as referring to something on a totally different sheet, which I didn't tell it to do...Deleting it brings the accursed "End If without Block If" error.

Meanwhile, can someone please diagnose my non-cooperative code:dunno ? I marked it solved too soon...:banghead:
http://www.vbaexpress.com/forum/showthread.php?t=27942&page=2

[lucas]

Is this a continuation of the question in the other thread? I removed the "solved" from your other thread.

[Gingertrees]

Thanks lucas!

I do actually have 2 questions: the "sort" question above, and the one on the other thread. I'd appreciate anyone's input on either or both. Thanks!

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Target.Count > 1 Then GoTo ws_exit
If Not Intersect(Target, Me.Range("NAMES")) Is Nothing Then

Me.Range("NAMES").EntireRow.Sort key1:=Me.Range("NAMES").Cells(1, 1), order1:=xlAscending, header:=xlNo
End If
ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub

[Gingertrees]

Am I to add that code to Malcolm's kbase code? Or instead of it? I notice it does not work alone...

[Bob Phillips]

Instead of. It worked alone on you workbook here.

[Gingertrees]

It is supposed to go in the Sheet 17(Cases) module?

[Bob Phillips]

Yes, it is, it is worksheet change event code.

[Gingertrees]

I think the fact that I have an offset dynamic range is screwing it up. For example, if I scroll down to c:358, delete the code there and manually change AAAA------- to WWWW------, the list reorders. But it does not recognize when the data in A5:A204 is changed - which is what would happen in use. Ideas?

[Bob Phillips]

No, it is not your range name, but the code. It looks specifically for a change in NAMES, which is column 4. Add an offset range name for column A and test that in the code instead.

[Gingertrees]

Ooo, ok. Now I see it working. I defined A4:A204 as "Urnlist", vs the dynamic range of C358:C557 "Names". Can I get it to only sort "Names", leaving "urnlist" unaffected?

[Bob Phillips]

AT what point would Names get sorted?

When you say only names, do you mean the rows containing NAMES?

[Gingertrees]

"Names" (C358:c557 only, not the rows that could screw up other coding) would be sorted whenever someone changes something in "Urnlist" (a5:a204).

Reason: Users do data entry in the range of a5:be204. "Names" is just the cleaned up source for the data validation drop downs on the other sheets.

[Bob Phillips]

Doesn't this do it



Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto ws_exit

Application.EnableEvents = False

If Target.Count > 1 Then Goto ws_exit
If Not Intersect(Target, Me.Range("Urnlist")) Is Nothing Then

Me.Range("NAMES").EntireRow.Sort key1:=Me.Range("NAMES").Cells(1, 1), order1:=xlAscending, header:=xlNo
End If
ws_exit:
Application.EnableEvents = True
On Error Goto 0
End Sub

[Gingertrees]

Two problems: I already have 1 worksheet_change event for that worksheet, and the range "NAMES" contains more code than values. For example, even though cell C358 looks like AAAA0202801 (or whatever), it's actually
{=IF(ISNUMBER($B358),INDIRECT("A"&$B358), "")}
I even tried adding a second name to the c358:c557 range, "SORTNAMES", and changed the 6th line of code accordingly, but still nothing.

Here is what's already in Cases:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Test As Long
Dim ChkDate As Long
Dim Age As Long
Dim Dte
If Target.Column <> 4 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Not IsDate(Target) Then
Dte = InputBox("Invalid date entered.", "Full Date", "mm/dd/yyyy")
If Dte = "" Or Not IsDate(Dte) Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
Else
Target = Dte
End If
End If
ChkDate = Date - DateValue("12/31/" & Year(Date) - 1)
Select Case Date - Target
Case Is < 0
Test = MsgBox("This date is in the future!", vbExclamation)
Target = InputBox("Please enter the date in full", "Full Date", "mm/dd/yyyy")
Case Is < ChkDate
Test = MsgBox("Is client less than one year old?", vbYesNo + vbDefaultButton2 + vbQuestion)
If Test = vbYes Then
Target.Offset(, 1).Select
Else
Target = InputBox("Please enter the date in full", "Full Date", "mm/dd/yyyy")
Target.Offset(, 1).Select
End If
Case Is > 365
'final check if date was entered correctly
Age = (Date - Target) / 365.25
Test = MsgBox("Is client approx. " & Age & " years old?", vbYesNo + vbDefaultButton2 + vbQuestion)
If Test = vbYes Then
Target.Offset(, 1).Select
Else
Target = InputBox("Please enter the correct date", "Full Date", "mm/dd/yyyy")
Target.Offset(, 1).Select
End If
End Select
End Sub

Anyway to combine these two and make them both work?