Edit/Update list from userform (auto alphabetized)

As the title suggests I have a sheet that creates a list of columns that may need to be updated or edited as necessary. Column A is a list of names that are alphabetized automaticaly from column D, so any change in column D would need to be reflected in column A. The attached sheet will (I hope) make things clearer.

Thanks

Jim O

Hello Jogier505,

I have revised your User Form and code to do what you asked. Column "A" has now been eliminated because the entries are now sorted when the user form opens, a new name is added, or a name is deleted. TextBox1 has been replaced by ComboBox1. This allows the user to either start typing in a name or select a name using the drop down.

Column "B" no longer contains any formulae. This column is filled automatically by the macro whenever the data changes. The formulae in column "E" remain intact.

Since you are opening the UserForm as non modal, I have added a code module to make the User Form act and look like a standard window. I have attached a jpg so you can see what the new form it looks like. The attached workbook contains all the changes.

User Form Code

Public R As Long
Public Wks As Excel.Worksheet

Sub LoadNames()
 'FILL THE COMBOBOX, SORT THE NAMES, AND ADD SEQUENTIAL ENTRY NUMBERS
 
  Dim Cell As Range
  Dim N As Long
  Dim Rng As Range
  Dim RngEnd As Range
      
    Set Rng = Wks.Range("D2:H2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
    
    Application.ScreenUpdating = False
    
      Rng.Sort Key1:=Rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo, _
               Orientation:=xlTopToBottom, MatchCase:=False
      
      ComboBox1.Clear
      For Each Cell In Rng.Columns(1).Cells
        N = N + 1
        Cell.Offset(0, -1) = N
        With ComboBox1
          .AddItem
            .List(.ListCount - 1, 0) = Cell.Value
            .List(.ListCount - 1, 1) = Cell.Row
        End With
      Next Cell
    
    Application.ScreenUpdating = True
    
End Sub

Private Sub CmdAdd_Click()
 'ADD NEW NAME
  
    If ComboBox1.Value = "" Then
       MsgBox "You must enter a name.", vbInformation
       Exit Sub
    End If
   
    Set Rng = Wks.Range("C2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(RngEnd, Rng))
          
   'switch off ScreenUpdating to speed up code & prevent flickering
    Application.ScreenUpdating = False

   'First empty row in database
    R = Rng.Row + Rng.Rows.Count

   'copy the data to the database
    With Wks.Rows(R).Cells
      .Item(1, 4).Value = ComboBox1.Value
      .Item(1, 5).FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1, 2 + 2 * ROWS(R1C:RC), 4), 1, """")"
      .Item(1, 6).Value = TextBox2.Value
      .Item(1, 7).Value = TextBox3.Value
        If CheckBox1 = True Then
           .Item(1, 8) = "PD"
        Else
           .Item(1, 8) = "No"
        End If
    End With

    LoadNames
    
End Sub

Private Sub ComboBox1_Click()
 'SELECT A NAME
  
    R = CLng(ComboBox1.List(ComboBox1.ListIndex, 1))
    
      With Wks
        ComboBox1 = .Cells(R, "D")
        TextBox2 = .Cells(R, "F")
        TextBox3 = .Cells(R, "G")
          If CheckBox1.Value = True Then
             .Cells(R, "H") = "PD"
          Else
             .Cells(R, "H") = "No"
          End If
      End With
      
End Sub

Private Sub CommandButton1_Click()
 'UPDATE WORKSHEET DATA
    
    If ComboBox1.ListIndex < 0 Then
       MsgBox "You must first select a name.", vbInformation
       Exit Sub
    End If
    
    R = CLng(ComboBox1.List(ComboBox1.ListIndex, 1))
    
      With Wks
        .Cells(R, "D") = ComboBox1
        .Cells(R, "F") = TextBox2
        .Cells(R, "G") = TextBox3
          If CheckBox1.Value = True Then
             .Cells(R, "H") = "PD"
          Else
             .Cells(R, "H") = "No"
          End If
      End With
      
End Sub

Private Sub CommandButton2_Click()
 'DELETE ENTRY
    
      If ComboBox1.ListIndex < 0 Then
         MsgBox "You must first select a name.", vbInformation
         Exit Sub
      End If
      
      With ComboBox1
        R = CLng(.List(.ListIndex, 1))
      End With
      
      TextBox1 = ""
      TextBox2 = ""
      TextBox3 = ""
      CheckBox1.Value = False
      
      Wks.Rows(R).EntireRow.Delete Shift:=xlShiftUp
      LoadNames
    
End Sub

Private Sub UserForm_Activate()

 'Add buttons to UserForm's title bar
  AddToForm MIN_BOX
  AddToForm MAX_BOX
  
 'Initialize the public variable Wks
  Set Wks = Worksheets("Lists")
  
 'Activate the worksheet and select "C2"
  Wks.Activate
  Wks.Range("C2").Select

 'Fill ComboBox1
  LoadNames
  
End Sub

Clear Player Data Macro

Sub ClearPlayerData()
  
  Dim Rng As Range
  Dim RngEnd As Range

    With Worksheets("Lists")
      Set Rng = .Range("C2:H2")
      Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp)
        If RngEnd.Row < Rng.Row Then Exit Sub  'No names have been entered
      .Range(Rng, RngEnd).Clear
    End With
    
End Sub