Using error handling to delete sheets based on listbox value

Question

So I have a listbox with a number of names and one value which is an "all names" option. When a name is selected, a worksheet is added with information about this name. When "all names" are selected, multiple worksheets are added, one for each name.

I am trying to put in some error handling that will delete the existing name sheet if someone selects the same one twice. I was able to get the code working for the "all names" function, to where it will delete all sheets except the dashboards/background data and then re-insert the name sheets. However, I am having trouble getting it to work where one single name is selected.

Here is my error handling code. I have it go to this OnError. For the second part, I am just using one of the potential name values, John. The sheet name and listbox value would be the same.

ErrorHandling:

If (Err.Number = 1004 And EmpListBox.Value = "All Names")Then
   For Each ws In Worksheets
     Select Case ws.Name
     Case "Dashboard", "Data", "Employees"
     Case Else
         ws.Delete
     End Select
   Next
'this is where it is not working
ElseIf (Err.Number = 1004 And EmpListBox.Value = "John") Then
    Sheets("John").Delete
Resume Main
End If

Use a function that returns a boolean if sheet exists and call that https://stackoverflow.com/q/6688131/6241235 you can then probably re-write the above and remove the current error handling. — QHarr, Nov 25 '18 at 04:44

score 1 · Accepted Answer · answered Nov 25 '18 at 09:21

1

ErrorHandling:

If Err.Number = 1004 Then
    If EmpListBox.Value = "All Names" Then
        For Each ws In Worksheets
            Select Case ws.Name
                Case "Dashboard", "Data", "Employees"
                Case Else
                    ws.Delete
            End Select
       Next
   Else
       Sheets(EmpListBox.Value).Delete
   End If
End If
Resume Main

answered Nov 25 '18 at 09:21

DisplayName

13,283
2
11
19

Thanks so much, this worked perfectly! Still threw in a sheet1, etc. but with a left function could easily delete those extra sheets. – tetriker Nov 25 '18 at 18:52

Using error handling to delete sheets based on listbox value

1 Answers1