Sorting a range alphabetically after input box adds new value

[ISqueal]

I'm creating a worksheet that has one column and one header for the column. There is a button that prompts an input box and creates a new row in the column for the inputted value. I am trying to create a procedure that sorts the column alphabetically after the input box value has been added as a new row however I can't seem to get it to work.

This is my current code:

Dim NewOwner As Variant


NewOwner = InputBox("Enter Owner Name", "Add New Owner")

Dim ownersht As Worksheet
Dim ownerLastRow As Long

Set ownersht = ThisWorkbook.Worksheets("Owners")
Set ownerLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Dim ownerRange As Range
Dim ownerkeycell As Range


Set ownerRange = Range(Cells(2, 1), Cells(ownerLastRow + 1, 1))
Set ownerkeycell = Cells(2, 1)


If NewOwner = vbOK Then
With ownersht
ownerRange.Sort Key1:=ownerkeycell, Order1:=xlAscending, Header:=xlYes
End With
End If

There is no error notification however the code just doesn't seem to work. Any help would be appreciated, thanks!

 

[JoeMo]

You have a few syntax errors. Untested, but see if this works:

Sub AddNewOwner()
Dim NewOwner As Variant
Dim ownersht As Worksheet
Dim ownerLastRow As Long
Dim ownerRange As Range
Dim ownerkeycell As Range

NewOwner = InputBox("Enter Owner Name", "Add New Owner")
If NewOwner = "" Then Exit Sub  'user clicked cancel
Set ownersht = ThisWorkbook.Worksheets("Owners")
ownerLastRow = ownersht.Cells(ownersht.Rows.Count, "A").End(xlUp).Row
Set ownerRange = Range(Cells(2, 1), Cells(ownerLastRow + 1, 1))
Set ownerkeycell = Cells(2, 1)
With ownersht
    ownerRange.Sort Key1:=ownerkeycell, Order1:=xlAscending, Header:=xlYes
End With
End Sub

This assumes your header is in A2. If its actually in A1 then change ownerrange to start at Cells(1,1).

 

[ISqueal]

Thank for the help! I didn't notice the Header issue and the exclusion of the If Then statement for the vbOk value made it work. Thanks so much!

 

[JoeMo]

Thank for the help! I didn't notice the Header issue and the exclusion of the If Then statement for the vbOk value made it work. Thanks so much!

You are welcome - thanks for the reply.