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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Any help is greatly appreciated.