speeding up users' deleting worksheets

[sahmiele]

I have a program that cycles through each worksheet tab and asks the user if he/she wants to delete the worksheet, and they answer yes or no and the program acts accordingly. What I would like to do is load all of the worksheet names into a form and have the user go through and check/highlight the worksheets they want to delete, and then hit ok, and have the program delete the tabs they selected. How would I go about doing this? Thanks in advance!

 

[xlbo]

what have you tried ??

As a starter, you may loop through the collection of worksheets and retrieve the Name property of each

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sahmiele]

I am currently looping through, getting the tab name, and prompting the user if they want to delete the tab. If they say yes, the tab is deleted, if they say no, the tab is not deleted. I would like to avoid the user getting prompted 40 or 50 times to answer yes or no for each tab.

I would like to have the program create a list box, or something where all of the tab names are displayed in one box, and the user can either click a check box next to the tabs they want to delete, or highlight all of the names of the tabs they want to delete. Then they hit the ok button, and the program deletes the tabs they selected.

I hope this makes sense.

 

[firefytr]

This will get you all the worksheets into an array..

    Dim arrSheets() As String, i As Long
    ReDim arrSheets(1 To ThisWorkbook.Worksheets.Count)
    For i = 1 To ThisWorkbook.Worksheets.Count
        arrSheets(i) = ThisWorkbook.Worksheets(i).Name
    Next i

You can set a userofrm listbox (with multi-select enabled) with the array, something like ..

Me.Lisbox1.value = arrSheets()

Then just loop through the listbox checking for the selected items and deleting them. Don't forget to leave a check in there for leaving at least one sheet in a workbook, else you may get an error.

HTH

-----------
Regards,
Zack Barresse

 

[CautionMP]

sahmiele,
You probably want a UserForm. The following code will take a blank UserForm, draw three controls on it and provide the basic functionality you have detailed.

Create a new UserForm in the VBE, toggle to the code pane of the newly created form and paste the following code into it, then run the form (crossing your fingers will help).

Option Explicit

Dim WithEvents CommandButton1 As CommandButton
Dim WithEvents CommandButton2 As CommandButton

Private Sub CommandButton1_Click()
'Delete button
Dim ctlListBox As ListBox
Set ctlListBox = Me.Controls("lstSheets")
Dim intItem As Integer
Application.DisplayAlerts = False
For intItem = 0 To ctlListBox.ListCount - 1
  If ctlListBox.Selected(intItem) Then
    ActiveWorkbook.Worksheets(ctlListBox.List(intItem)).Delete
  End If
Next intItem
Set ctlListBox = Nothing
Unload Me
End Sub

Private Sub CommandButton2_Click()
'Cancel button
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wksCurrent As Worksheet
Dim ctlNew As Control
'Resize the form
With Me
  .Height = 195
  .Width = 309
End With

'list box stufff
Set ctlNew = Controls.Add("Forms.ListBox.1", "lstSheets", True)
With ctlNew
  .Height = 129.75
  .Left = 8.25
  .Top = 15
  .Width = 290.25
  .MultiSelect = fmMultiSelectMulti
End With
For Each wksCurrent In ActiveWorkbook.Worksheets
  ctlNew.AddItem wksCurrent.Name
Next wksCurrent
'Delete button
Set CommandButton1 = Controls.Add("Forms.CommandButton.1", "CommandButton1", True)
With CommandButton1
  .Height = 21
  .Left = 105
  .Top = 150
  .Width = 48
  .Caption = "Delete"
End With
'Cancel button
Set CommandButton2 = Controls.Add("Forms.CommandButton.1", "CommandButton1", True)
With CommandButton2
  .Height = 21
  .Left = 159
  .Top = 150
  .Width = 48
  .Caption = "Cancel"
End With
End Sub

I had to jump through a couple of hoops to make this work since I am creating the controls at Runtime (it's easier to do at design time), but it should demonstrate one way to make this work.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

@CautionMP: I would still recommend just creating them and not at runtime, seems an unecessary waste of resources. Also, it will error out if all sheets are selected (assuming no other sheet types are present).

-----------
Regards,
Zack Barresse

 

[CautionMP]

[navy]firefytr[/navy],
Ya' think?

I created the controls at runtime because it's portable, you can copy the code into a blank form, and when run you pretty much see exactly what I saw. In this instance it was easier than detailing out the controls, and the event procedures will run with controls created at design time (if the names are correct).

Good point on the error, [navy]sahmiele[/navy] if you go this route it would probably be good to make sure all the worksheets are not selected when the user goes to delete.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

Uh, I do think. I don't see how programming the controls at runtime is being efficient, that's all. Portability? Maybe, but that was never expressed by the OP. And portable for what? If it was desired to be portable, why not create an addin??

-----------
Regards,
Zack Barresse