List Worksheets and select one (or more)

[sspicer]

I am sorry if I am repeating a thread here, but I've tried to find something that will help me, and can't.

I have a model which produces a new worksheet each time it is run - and old sheets are hidden from the user.

I need to write some code that will take the names of all spreadsheets and list them in a userform (or similar) so that the user can then click on the ones they wish to delete and press delete (i.e. be able to select more than one if they wish).

I am happy with being able to assign the commands to buttons once sheet names have been selected, but I don't known how to get the names of the sheets in the form in the first place.

Any help? Please feel free to point me in the direction of another thread if I've missed something.

Thanks for your help in advance.

 

[Bill Rockenbach]

Create a userform with a listbox set to multiselect

Put this code in the userfrom code module it will build your list of sheets

Option Explicit
Private Sub UserForm_Initialize()
    Dim sht As Worksheet
    ListBox1.MultiSelect = fmMultiSelectMulti
    For Each sht In Worksheets
        ListBox1.AddItem sht.Name
    Next sht
End Sub

 

[Bill Rockenbach]

Next: Place a button on the sheet to activate the deletion of the selected sheets to be deleted.

Place this code in the userform code module

Private Sub CommandButton1_Click()
Dim lItem As Long
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheets(ListBox1.List(lItem)).Delete
            ListBox1.Selected(lItem) = False
        End If
    Next
End Sub

 

[sspicer]

That's fantastic and works a treat! cheers, S