Using VBA to name a range and delete a value instead of named range in wkbk

...a continuation from this thread.

In summary, it seems the .list property (and variants, such as .listindex) don't like it when the "list" is only a single value. So in the linked thread above, I solved my dependence on a named dynamic range in my workbook by using code to name my range, and counting the rows of the range... if rows count = 1 (which will causes an error in the .list property), then do <this> action, but if rows count > 1, then we can use .list.

So I have an employee list in ws("Admin Menu") in column B. Column A contains a record number (1, 2, 3...) of the number of employees. The list begins on row 2 underneath a header row, and $B$2 is always "Former Employee." In my named dynamic range, the range begins on $B$3. I have a userform to delete an employee. It populates a combo box with a list of the employees, and once one is selected, first finds all matching employees in the worksheet("Data") and changes that employee's names to "Former Employee" - this way data associated with that employee is kept, but records for that employee name are not - and then deletes the employees name from the list on the ws("Admin Menu"). Here's my code:

Private Sub UserForm_Initialize()
  
  Dim rngDelEmpList As Range

On Error Resume Next
  
  With Sheets("Admin Menu")
    Set rngDelEmpList = .Range("B3", .Range("B" & Rows.Count).End(xlUp))
  If rngDelEmpList.Rows.Count = 1 Then
    Me.cbo5.AddItem rngDelEmpList
  Else
    Me.cbo5.List = rngDelEmpList.Value
  End If
  End With

End Sub

Private Sub DelCancel_Click()

  Unload Me

End Sub

Private Sub DelEmp_Click()
  Dim answer As VbMsgBoxResult
  Dim c As Long

On Error Resume Next

  If cbo5.ListIndex = -1 Then
    MsgBox "No employee selected."
  Else

    answer = MsgBox("Are you sure you want to delete " & cbo5.Value & "?", vbYesNo + vbExclamation, "Delete Employee")
    If answer = vbYes Then

      With ws3

        Worksheets("Data").Range("EmpNameData").Replace _
            What:=cbo5.Value, Replacement:="Former Employee", _
            SearchOrder:=xlByColumns
      End With

      Range("EmpDelList").Cells(cbo5.ListIndex + 1, 1).Delete xlShiftUp
      Range("A" & Cells.Rows.Count).End(xlUp).Select
      Selection.ClearContents

      MsgBox cbo5.Value & " has been deleted."
      Unload Me
    End If
  End If

End Sub

I would like to change my routine for the DelEmp_Click() by NOT using the named range "EmpDelList" and instead naming the range with the VBA code used in the initialization (I am keeping the "EmpNameData" range - at least for now)... but the listindex property keeps tripping me up. Any help?

Again, sorry for not being able to upload a sample file at this time!!

-HeyInKy

Sample Project.xlsm

Use the "Add Employee" button to add the following employees: Employee 1, Employee 2, Employee 3, and Employee 4. (These are already pre-listed on the "Data" worksheet).

I need the "Delete Employee" to use code - not named ranges in the workbook formulas - to populate the list, and "Former Employee" should not show up either. Then once a name is selected and the delete command is executed, it should remove the selected employee from the list on the "Admin Menu" as well as the corresponding number, and then move everything up 1 row (if the deleted employee was not on the last row).

It also - and it does this correctly now - find all matching names on the "data" worksheet and change them to "Former Employee."

Still trying to fix this... here's where I'm at for the delete command button:

Private Sub DelEmp_Click()
  Dim answer As VbMsgBoxResult
  Dim c As Long
  Dim rngDelEmpList As Range

On Error Resume Next

  If cbo5.Value = "Former Employee" Then
    MsgBox "You cannot delete a 'Former Employee.'"
  End If

  If cbo5.Value = "" Then
    MsgBox "No employee selected."
  Else

    answer = MsgBox("Are you sure you want to delete " & cbo5.Value & "?", vbYesNo + vbExclamation, "Delete Employee")
    If answer = vbYes Then

      With ws3

        Worksheets("Data").Range("EmpNameData").Replace _
            what:=cbo5.Value, Replacement:="Former Employee", _
            SearchOrder:=xlByColumns
      End With

  
  c = Worksheets("Administrative Menu").Range("B3").CurrentRegion.Rows.Count
  Set Found = Worksheets("Administrative Menu").Columns("B").Find(what = Me.cbo5.Value, LookIn:=xlValues, lookat:=xlWhole)
  Found.Delete (xlShiftUp)
  
      
      MsgBox cbo5.Value & " has been deleted."
      Unload Me
    End If
  End If

End Sub

Any help is greatly appreciated.