Delete Sheets

[hOSAMnOOR]

Hi

How I can Delete The Sheets through Code ? With msg box confermation ?

Thanks

 

[Peter_SSs]

Hi

How I can Delete The Sheets through Code ? With msg box confermation ?

Thanks

Not quite clear to me, but if you want to avoid the message box, something like this ...

Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True

 

[jindon]

Something like this

If vbYes = MsgBox("Delete " & ActiveSheet.Name & " ?",vbYesNo + vbCritical) Then
    On Error Resume Next
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
End If

 

[VoG]

Or perhaps

Sub DelSh()
Dim ws As Worksheet, i As Integer, Response As VbMsgBoxResult
For Each ws In ThisWorkbook.Worksheets
    If Worksheets.Count > 1 Then
        Response = MsgBox("Delete sheet " & ws.Name & " ?", vbYesNo + vbQuestion)
        If Response = vbYes Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    End If
Next ws
End Sub

 

[hOSAMnOOR]

Thanks For All

I need code with msgbox include with list box with sheets name , then i can choose the sheet name

but sheet 1 cannot be delete because it's the main page

thanks again for all

 

[jindon]

Create a userform with

1 x ListBox (ListBox1)
1 x CommandButton (CommandButton1)

to the form module

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Sheets.Count
    Me.ListBox1.AddItem Sheets(i).Name
Next
End Sub
 
Private Sub CommandButton1_Click()
With Me.ListBox
    If .ListIndex = -1 Then Exit Sub
    If vbYes = MsgBox("Delete " & .Value & " ?",vbYesNo + vbCritical) Then
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(.Value).Delete
        Application.DisplayAlerts = True
        If Err <> 0 Then MsgBox "You need at least one sheet"
        On Error GoTo 0
    End If
End With
End Sub

 

[hOSAMnOOR]

Thanks jindon<SCRIPT type=text/javascript> vbmenu_register("postmenu_1785091", true); </SCRIPT>

Very Nice Code

 

[jindon]

Correction:

With Me.ListBox

should be

With Me.ListBox1

 

[hOSAMnOOR]

Correction:

With Me.ListBox

should be

With Me.ListBox1

Yes I changed already

Thanks

 

[twothings]

I have attempted to adjust this code to only provide option to delete the active sheet, and not provide an option for all sheets in the workbook. I am unable to make adjustments without causing an error.

All I aim to achieve is to delete the ActiveSheet and the prompt confirmation to include the worksheet name...i.e.

Response = MsgBox("Delete sheet " & ws.Name & " ?", vbYesNo + vbQuestion)

Or perhaps

Sub DelSh()
Dim ws As Worksheet, i As Integer, Response As VbMsgBoxResult
For Each ws In ThisWorkbook.Worksheets
    If Worksheets.Count > 1 Then
        Response = MsgBox("Delete sheet " & ws.Name & " ?", vbYesNo + vbQuestion)
        If Response = vbYes Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    End If
Next ws
End Sub