Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
I want to be able to delete all sheets from a workbook
except a few specific ones. Is there any way to get a list of current sheets in a form where I can delete the names I want to keep and select the rest? TIA peterG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
Pete
The following will loop through the sheets in the current workbook and show the names. You should be able to use this to determine the list of names you want to delete. Ton Sub ccc( For Each na In ActiveWorkbook.Sheet MsgBox na.Nam Next n End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
PeterG,
Here's one way: Sub testit() Dim wks As Worksheet, arr() As Worksheet, i As Long, blnTemp As Boolean ReDim arr(0) For Each wks In Worksheets Select Case wks.Name Case "Sheet2" Case Else i = UBound(arr) + 1 ReDim Preserve arr(i) Set arr(i) = wks End Select Next blnTemp = Application.DisplayAlerts Application.DisplayAlerts = False For i = 1 To UBound(arr) arr(i).Delete Next Application.DisplayAlerts = blnTemp End Sub Rob "peterG" wrote in message ... I want to be able to delete all sheets from a workbook except a few specific ones. Is there any way to get a list of current sheets in a form where I can delete the names I want to keep and select the rest? TIA peterG |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
Peter,
Please test this first on a test workbook or two. This will keep the sheets you select in the userform and delete the rest. Note that you can select groups of sheets in the userform by using the Control or Shift keys. Create a UserForm1 with a ListBox1 and a CommandButton1 and put the code below inside the UserForm1: Private Sub UserForm_Initialize() Dim sh As Worksheet Me.ListBox1.MultiSelect = fmMultiSelectExtended For Each sh In ActiveWorkbook.Worksheets Me.ListBox1.AddItem (sh.Name) Next sh End Sub Private Sub CommandButton1_Click() Dim sheets_selected, delete_sheets As Boolean Dim i As Integer sheets_selected = False delete_sheets = False With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) = True Then sheets_selected = True Exit For End If Next i End With If sheets_selected Then delete_sheets = MsgBox(prompt:="Selected Sheets Will Be Kept" & vbCrLf & _ "Those NOT Selected Will be DELETED", _ Buttons:=vbOKCancel + vbExclamation, _ Title:="Delete UnSelected Sheets") - 2 If delete_sheets Then With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) = False Then Application.DisplayAlerts = False ActiveWorkbook.Worksheets(.List(i)).Delete Application.DisplayAlerts = True End If Next i End With End If End If Unload Me End Sub Call the form in a regular module with: UserForm1.Show hth, Doug "peterG" wrote in message ... I want to be able to delete all sheets from a workbook except a few specific ones. Is there any way to get a list of current sheets in a form where I can delete the names I want to keep and select the rest? TIA peterG |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
Here's a macro that displays a list of the worksheets in the workbook.
Select the sheets, click the button and they will be removed. Deleting sheets is not a reversible action so save a backup workbook. Create a userform with a listbox and command button, UserForm1, ListBox1, CommandButton1. Code goes in the form. Private Sub UserForm_Initialize() Call RefreshList CommandButton1.Caption = "Delete Selection" ListBox1.MultiSelect = fmMultiSelectExtended End Sub Sub RefreshList() ListBox1.Clear Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets ListBox1.AddItem wks.Name Next End Sub Private Sub CommandButton1_Click() Dim i As Integer For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then If MsgBox("Warning. This will remove the selected sheets. Continue?", vbYesNo) = _ vbYes Then Application.DisplayAlerts = False ThisWorkbook.Worksheets(ListBox1.List(i)).Delete Application.DisplayAlerts = True End If End If Next i Call RefreshList End Sub "peterG" wrote in message ... I want to be able to delete all sheets from a workbook except a few specific ones. Is there any way to get a list of current sheets in a form where I can delete the names I want to keep and select the rest? TIA peterG |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
Correction. Use this one instead. Last one's message was misplaced.
Private Sub UserForm_Initialize() Call RefreshList CommandButton1.Caption = "Delete Selection" ListBox1.MultiSelect = fmMultiSelectExtended End Sub Sub RefreshList() ListBox1.Clear Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets ListBox1.AddItem wks.Name Next End Sub Private Sub CommandButton1_Click() If MsgBox("Warning. This will remove the selected sheets. Continue?", _ vbYesNo) = vbNo Then Exit Sub Dim i As Integer For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then Application.DisplayAlerts = False ThisWorkbook.Worksheets(ListBox1.List(i)).Delete Application.DisplayAlerts = True End If Next i Call RefreshList End Sub "Tim Zych" wrote in message ... Here's a macro that displays a list of the worksheets in the workbook. Select the sheets, click the button and they will be removed. Deleting sheets is not a reversible action so save a backup workbook. Create a userform with a listbox and command button, UserForm1, ListBox1, CommandButton1. Code goes in the form. Private Sub UserForm_Initialize() Call RefreshList CommandButton1.Caption = "Delete Selection" ListBox1.MultiSelect = fmMultiSelectExtended End Sub Sub RefreshList() ListBox1.Clear Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets ListBox1.AddItem wks.Name Next End Sub Private Sub CommandButton1_Click() Dim i As Integer For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then If MsgBox("Warning. This will remove the selected sheets. Continue?", vbYesNo) = _ vbYes Then Application.DisplayAlerts = False ThisWorkbook.Worksheets(ListBox1.List(i)).Delete Application.DisplayAlerts = True End If End If Next i Call RefreshList End Sub "peterG" wrote in message ... I want to be able to delete all sheets from a workbook except a few specific ones. Is there any way to get a list of current sheets in a form where I can delete the names I want to keep and select the rest? TIA peterG |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in vb
Slightly updated version of Tim's code in case they try and delete all
sheets gives a warning and exits Option Explicit Private Sub UserForm_Initialize() Call RefreshList CommandButton1.Caption = "Delete Selection" ListBox1.MultiSelect = fmMultiSelectExtended End Sub Sub RefreshList() ListBox1.Clear Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets ListBox1.AddItem wks.Name Next End Sub Private Sub CommandButton1_Click() Dim i As Integer Dim fAllSelected As Boolean fAllSelected = True For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = False Then Exit For End If Next i If fAllSelected Then MsgBox "You cannot delete all of the sheets", vbExclamation Exit Sub End If If MsgBox("Warning. This will remove the selected sheets. Continue?", _ vbYesNo) = vbNo Then Exit Sub For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then Application.DisplayAlerts = False ThisWorkbook.Worksheets(ListBox1.List(i)).Delete Application.DisplayAlerts = True End If Next i Call RefreshList End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tim Zych" wrote in message ... Correction. Use this one instead. Last one's message was misplaced. Private Sub UserForm_Initialize() Call RefreshList CommandButton1.Caption = "Delete Selection" ListBox1.MultiSelect = fmMultiSelectExtended End Sub Sub RefreshList() ListBox1.Clear Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets ListBox1.AddItem wks.Name Next End Sub Private Sub CommandButton1_Click() If MsgBox("Warning. This will remove the selected sheets. Continue?", _ vbYesNo) = vbNo Then Exit Sub Dim i As Integer For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then Application.DisplayAlerts = False ThisWorkbook.Worksheets(ListBox1.List(i)).Delete Application.DisplayAlerts = True End If Next i Call RefreshList End Sub "Tim Zych" wrote in message ... Here's a macro that displays a list of the worksheets in the workbook. Select the sheets, click the button and they will be removed. Deleting sheets is not a reversible action so save a backup workbook. Create a userform with a listbox and command button, UserForm1, ListBox1, CommandButton1. Code goes in the form. Private Sub UserForm_Initialize() Call RefreshList CommandButton1.Caption = "Delete Selection" ListBox1.MultiSelect = fmMultiSelectExtended End Sub Sub RefreshList() ListBox1.Clear Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets ListBox1.AddItem wks.Name Next End Sub Private Sub CommandButton1_Click() Dim i As Integer For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then If MsgBox("Warning. This will remove the selected sheets. Continue?", vbYesNo) = _ vbYes Then Application.DisplayAlerts = False ThisWorkbook.Worksheets(ListBox1.List(i)).Delete Application.DisplayAlerts = True End If End If Next i Call RefreshList End Sub "peterG" wrote in message ... I want to be able to delete all sheets from a workbook except a few specific ones. Is there any way to get a list of current sheets in a form where I can delete the names I want to keep and select the rest? TIA peterG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting sheets - II | Excel Discussion (Misc queries) | |||
Selecting sheets | Excel Discussion (Misc queries) | |||
selecting sheets | Excel Programming | |||
Changing the value in multiple sheets without selecting those sheets | Excel Programming | |||
selecting sheets | Excel Programming |