Not knowing exactly how you're getting the new entry for the list and
where/how your current code to add to the list works, I've come up with this
code that you should be able to adapt and 'blend' with what you have now to
do the job.

Sub AddToList()
Const ListSheet = "List" ' change as needed
Const ListColumn = "E" ' change as needed
Dim NewItem As String
Dim lastUsedRow As Long
Dim SortKey As String
Dim StartSheet As String
Dim StartLocation As String

StartSheet = ActiveSheet.Name
StartLocation = ActiveCell.Address
' make actions invisible to user
Application.ScreenUpdating = False
Worksheets(ListSheet).Visible = True
Worksheets(ListSheet).Select

NewItem = "dale" ' or however else you get it

'find row# 1 row above last used row
lastUsedRow = Worksheets(ListSheet). _
Range(ListColumn & Rows.Count).End(xlUp). _
Offset(-1, 0).Select
Selection.Insert Shift:=xlDown
ActiveCell = NewItem

SortKey = ListColumn & "1"
Range(ListColumn & "1:" & ActiveCell.Address).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range(SortKey).Select ' for neatness
'back to original location
Worksheets(StartSheet).Select
Worksheets(ListSheet).Visible = False ' hide again
Range(StartLocation).Activate
Application.ScreenUpdating = True

End Sub

" wrote:

I've got a handful of issues to work through with a particular project
I'm working on but let's start with the first: reordering part of a
list with VBA.

I have a list on a hidden tab (let's be adventurous and call it the
"List" tab) which prepopulates a drop-down list for data entry on
another tab (the "Entry" tab, shocking!). However, a user has the
option of entering a value not on the drop-down menu, if need be. I've
got a Worksheet_Change function that will append this new value to the
bottom of the list, which of course means it now appears on the drop-
down menu. So far, so good, except I would like to resort the list by
alpha after appending the new record.

"Aha! Easy!" you say, but not so fast. There's a catch - I always want
two values ("N/A" and "<Enter New Item") to be at the very bottom of
the menu.

Sooooo, how do I take a list, append a record to it, and then resort
it, except the records that were formerly the last and second-to-last
- so as to keep them at the bottom of the new list? I can come up some
brilliantly convoluted ways of doing this, but hopefully there's
something simpler.

Thanks!

..o.