I am relatively new to VBA/Macros in excel. I have spent the day attempting to problem solve but now I am requesting help.

I have two worksheets, (1) the database and (2) setup sheet. The setup sheet has columns with lists that are used to create drop down menus in my database sheet. I wish for my drop down menus to be sorted alphabetically as this is a long term project.

Basically Column C of setup sheet holds all my scientific names. Each time I enter a new name I would like this column to automatically be sorted in ascending order. I can write a macro or VBA to sort column C but I cannot get it to do it automatically when a new cell has text entered. Could someone assist with this?

I have tried many iterations of the following code, what have I done incorrectly?

Sub ColC() 
Dim ws As Worksheet 
Dim columnCRng As Range 
Dim changed As Range 
Dim cell As Range 
Dim hasText As Boolean

' Set the worksheet object
Set ws = ThisWorkbook.Worksheets("Key")

' Define the range for column C
Set columnCRng = ws.Columns("C:C")


' Check if the changed cell is within column C
Set changed = Intersect(Target, columnCRng)
If Not changed Is Nothing Then
    Application.EnableEvents = False

'Check if any cell in column C has text 
For Each cell In columnCRng If Not IsEmpty(cell.Value) And Not IsNumeric(cell.Value) 
Then hasText = True Exit For End If Next cell

' If any cell has text, sort column C in ascending order
If hasText Then
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=columnCRng, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange columnCRng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If

Application.EnableEvents = True

End Sub