Userform Add variable into list in Alphabetical position

[Paul1005]

Hi,
I'm trying to create a Userform so i can input several peices of data into a reference sheet (£) and it will then insert into a list in the correct position Alphabetcially. For example if i wanted to add "Tesla" into the below list it should be added inbetween SmithDS & United Utilities.

Segro	SGRO	79​
Smith DS	SMDS	93​
United Utilities	UU.	35​
Vistry Group	VTY	95​

I have a LOT of formaula that pull data from this sheet into various other sheets so i dont really want to add to the bottom then reorder as i dont know what the implications on the other sheets would be.

I can do this with a Input Box option but there are several Variables so its not that user friendly - the code that works is:-

Sub InsertCompany()
Dim sNewName As String
Dim sCoNo As String
Dim TCKR As String
Dim lPosition As Long
Dim rEmpList As Range

' Add new Company with Input Boxes

Sheets("£").Select

Set rEmpList = Sheets("£").Range("A:A")

sNewName = InputBox("Enter name of new Company")
sCoNo = InputBox("Enter Company #")
TCKR = InputBox("Enter TCKR reference")
L

On Error Resume Next
lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
On Error GoTo 0
Rows(lPosition + 1).Insert
Range("A" & lPosition + 1).Value = sNewName
Range("B" & lPosition + 1).Value = TCKR
Range("C" & lPosition + 1).Value = sCoNo

End Sub


Then i created a userform with same Variables above and tried to use the same code to insert the values entered into the userform fields but this just adds a blank row in Row1 - it doesnt add any of the text input and doesnt add the rown in the correct position alphabetically. I'm totally stumped, can anyone help me please and point out where i've gone wrong?

Many thanks
Paul

 

[ranman256]

add it to the bottom , then sort.

 

[Paul1005]

I was trying to avoid adding it to the bottom and then sorting.
I've got a work around which is to save the Userform values to a temporary location then copy these values in using an extract of the working input message code before deleting the values in the temporary location - while it works its a little clunky and not that elegant of a solution.