Prevent duplicates from being entered from a user form. Then sort the table being used in ascending order based on column to the left

[chips]

Hello

I have a userform and various tables

the userform has 3 listboxes lbxANADate, lbxANAName, and lbxANAShift . lbxANAName is multioption

the form asks for a date, shift time and Name
there can be more than one name for each shift

the code below works really well. But is there a way to check if the name already exists in the lbxANAName column and give a warning that stops a duplicate from happening allowing the user to reenter another name?

i also then want to sort by the column that lbxANAShift data is placed in.

Private Sub btnANAOk_Click()
Dim sDate As String, sShift As String
  Dim i As Long, col As Long, lr As Long
  Dim f As Range
  Dim Sh As Worksheet
 
  If lbxANADate.ListIndex = -1 Then
    MsgBox "Select date"
    Exit Sub
  End If
  If lbxANANames.ListIndex = -1 Then
    MsgBox "Select Name"
    Exit Sub
  End If
  If lbxANAShift.ListIndex = -1 Then
    MsgBox "Select Shifts"
    Exit Sub
  End If
  Set Sh = Sheets("ANAES Data Entry")
 
  sDate = Format(CDate(lbxANADate.value), "ddd dd/mm")
 

sShift = lbxANAShift.value
  Set f = Sh.Rows(8).Find(sDate, , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    col = f.Column
    lr = Sh.Columns(col).Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
    For i = 0 To lbxANANames.ListCount - 1
      If lbxANANames.Selected(i) Then
        Sh.Cells(lr, col).value = sShift
        Sh.Cells(lr, col + 2).value = lbxANANames.List(i, 0)
       
        lr = lr + 1
      End If
    Next
  Else
    MsgBox "Date " & sDate & " does not exist"
  End If
End Sub

 

[chips]

Ok I figured it out ...seems to be working

Private Sub btnANAOk_Click()
Dim sDate As String, sShift As String
  Dim i As Long, col As Long, lr As Long
  Dim f As Range
  Dim Sh As Worksheet
 Dim col2 As Long
 Dim tablename As ListObject
 Dim columnstosortby As Range
 'checks if all boxes are ticked
  If lbxANADate.ListIndex = -1 Then
    MsgBox "Select date"
    Exit Sub
  End If
  If lbxANANames.ListIndex = -1 Then
    MsgBox "Select Name"
    Exit Sub
  End If
  If lbxANAShift.ListIndex = -1 Then
    MsgBox "Select Shifts"
    Exit Sub
  End If
 
 
  Set Sh = Sheets("ANAES Data Entry") 'saves typing the whole sheet name in the future by assigning it to a variable
 
 'formats the date so it matches the date on the worksheet
  sDate = Format(CDate(lbxANADate.value), "ddd dd/mm")
 
'moves the shift selection to a range variable sShift to be used later
sShift = lbxANAShift.value

'finds the date in the worksheet
  Set f = Sh.Rows(8).Find(sDate, , xlValues, xlPart, , , False)
 
'If the date exists go to the next row down where the values are held
  If Not f Is Nothing Then
   
                'sets the column number where the date is
                col = f.Column
           col2 = col + 2
                'finds the next empty cell in the column and assigns that row number to the variable lr
                    lr = Sh.Columns(col).Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
            
                'sets up a loop to search through all of the Names in the list box lbxANANames to see what has been selected
                            For i = 0 To lbxANANames.ListCount - 1  'finds out how many items are in Names which control how many times the if question is performed
                               
                                            If lbxANANames.Selected(i) Then  'if the name is selected then
                                           
                                                    If Application.WorksheetFunction.CountIf(Sh.Range(Cells(8, col2), Cells(lr, col2)), lbxANANames.List(i, 0)) > 0 Then
                                                   
                                                    MsgBox lbxANANames.List(i, 0) & " Has already been added, please select another name"

                                                    Else
                                                                                                                                            
                                                                         Sh.Cells(lr, col).value = sShift 'puts the value from the selected shift into the cell
                                                                         Sh.Cells(lr, col + 2).value = lbxANANames.List(i, 0) ' goes down i rows and across 2 columns and places the name in that cell
                                   
                                   Sh.Cells(lr, col).Select
                                  
                                   
                                    Set tablename = ActiveCell.ListObject
                                    Set columnstosortby = tablename.ListColumns(2).Range
                              
                               With tablename.Sort
                                        .SortFields.Clear
                                        .SortFields.Add columnstosortby, Order:=xlAscending
                                        .Header = xlYes
                                        .Apply
                                End With

                                                                         lr = lr + 1
                                                     End If

                                            End If   ' restarts the routine and goes to the next name in the list

                            Next

  Else  ' if there is no date that matches
    MsgBox "Date " & sDate & " does not exist try again"
   
  End If
 
End Sub