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

New Member
Joined
Oct 21, 2008
Messages
43
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

chips

New Member
Joined
Oct 21, 2008
Messages
43
Ok I figured it out ...seems to be working

VBA Code:
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
 
Last edited by a moderator:
Solution

Forum statistics

Threads
1,136,864
Messages
5,678,210
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top