VBA msgbox

[yomomo]

So i made this sub that makes a new sheet called "Statistics" in which there are some pivot tables. I made it so i was able to overwrite the sheet if it already existed. Now i'm trying to figure out how to make a msgbox that asks the user if he is sure that he wants to overwrite the sheet, if it already exist?

My code looks like this:

 Sub Opgave3()


Dim newSheet As Worksheet


Application.DisplayAlerts = False


Set newSheet = Sheets.Add(After:=ActiveSheet)
With newSheet
    On Error Resume Next
    ThisWorkbook.Sheets("Statistics").Delete
    On Error GoTo 0
    .Name = "Statistics"
End With


Application.DisplayAlerts = True


Application.DisplayAlerts = True
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Base!R1C1:R18288C12", Version:=6).CreatePivotTable TableDestination:= _
        "Statistics!R1C1", TableName:="Pivottabel22", DefaultVersion:=6
    Sheets("Statistics").Select
    Cells(1, 1).Select
    ActiveSheet.PivotTables("Pivottabel22").AddDataField ActiveSheet.PivotTables( _
        "Pivottabel22").PivotFields("FACULTY_ID"), "Antal af FACULTY_ID", xlCount
    With ActiveSheet.PivotTables("Pivottabel22").PivotFields("FACULTY_ID")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivottabel22").PivotFields("PROGRAM_TYPE_NAME")
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("A1").Select
    ActiveSheet.PivotTables("Pivottabel22").DataPivotField.PivotItems( _
        "Antal af FACULTY_ID").Caption = "Antal"
    Range("B1").Select
    ActiveSheet.PivotTables("Pivottabel22").CompactLayoutColumnHeader = "Fakultet"
    Range("A7").Select
    ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel22").PivotCache. _
        CreatePivotTable TableDestination:="Statistics!R7C1", TableName:= _
        "Pivottabel23", DefaultVersion:=6
    Sheets("Statistics").Select
    Cells(7, 1).Select
    ActiveSheet.PivotTables("Pivottabel23").AddDataField ActiveSheet.PivotTables( _
        "Pivottabel23").PivotFields("FACULTY_ID"), "Antal af FACULTY_ID", xlCount
    With ActiveSheet.PivotTables("Pivottabel23").PivotFields("PROGRAM_TYPE_NAME")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivottabel23").PivotFields("FACULTY_ID")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivottabel23").PivotFields("Antal af FACULTY_ID")
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0.00%"
    End With
    Range("A7").Select
    ActiveSheet.PivotTables("Pivottabel23").DataPivotField.PivotItems( _
        "Antal af FACULTY_ID").Caption = "Procentvis"
    Range("B7").Select
    ActiveSheet.PivotTables("Pivottabel23").CompactLayoutColumnHeader = "Fakultet"
    Range("A13").Select
    ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel23").PivotCache. _
        CreatePivotTable TableDestination:="Statistics!R13C1", TableName:= _
        "Pivottabel24", DefaultVersion:=6
    Sheets("Statistics").Select
    Cells(13, 1).Select
    ActiveSheet.PivotTables("Pivottabel24").AddDataField ActiveSheet.PivotTables( _
        "Pivottabel24").PivotFields("ENROLL_LOCATION_NAME"), _
        "Antal af ENROLL_LOCATION_NAME", xlCount
    With ActiveSheet.PivotTables("Pivottabel24").PivotFields("ENROLL_LOCATION_NAME" _
        )
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("B13").Select
    ActiveSheet.PivotTables("Pivottabel24").DataPivotField.PivotItems( _
        "Antal af ENROLL_LOCATION_NAME").Caption = "Antal"
    Range("A13").Select
    ActiveSheet.PivotTables("Pivottabel24").CompactLayoutRowHeader = "Campus"
    Range("B13").Select
    ActiveSheet.PivotTables("Pivottabel24").DataPivotField.PivotItems("Antal"). _
        Caption = "Antal af studerende"
    Range("A22").Select
    ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel24").PivotCache. _
        CreatePivotTable TableDestination:="Statistics!R22C1", TableName:= _
        "Pivottabel25", DefaultVersion:=6
    Sheets("Statistics").Select
    Cells(22, 1).Select
    ActiveSheet.PivotTables("Pivottabel25").AddDataField ActiveSheet.PivotTables( _
        "Pivottabel25").PivotFields("ENROLL_LOCATION_NAME"), _
        "Antal af ENROLL_LOCATION_NAME", xlCount
    With ActiveSheet.PivotTables("Pivottabel25").PivotFields("ENROLL_LOCATION_NAME" _
        )
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivottabel25").PivotFields( _
        "Antal af ENROLL_LOCATION_NAME")
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0.00%"
    End With
    Range("A22").Select
    ActiveSheet.PivotTables("Pivottabel25").CompactLayoutRowHeader = "Campus"
    Range("B22").Select
    ActiveSheet.PivotTables("Pivottabel25").DataPivotField.PivotItems( _
        "Antal af ENROLL_LOCATION_NAME").Caption = "Procentvis af studerende"
    Range("I1").Select
    ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel25").PivotCache. _
        CreatePivotTable TableDestination:="Statistics!R1C9", TableName:= _
        "Pivottabel26", DefaultVersion:=6
    Sheets("Statistics").Select
    Cells(1, 9).Select
    ActiveSheet.PivotTables("Pivottabel26").AddDataField ActiveSheet.PivotTables( _
        "Pivottabel26").PivotFields("STUDYBOARD_ID"), "Antal af STUDYBOARD_ID", xlCount
    With ActiveSheet.PivotTables("Pivottabel26").PivotFields("STUDYBOARD_ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("I1").Select
    ActiveSheet.PivotTables("Pivottabel26").CompactLayoutRowHeader = "Studienævn"
    Range("J1").Select
    ActiveSheet.PivotTables("Pivottabel26").DataPivotField.PivotItems( _
        "Antal af STUDYBOARD_ID").Caption = "Antal af studerende"
    Range("L15").Select
End Sub

 

[Yongle]

You could use something like this:
- variable sName added to prevent repetition

Dim newSheet As Worksheet
Dim sName As String:    sName = "Statistics"

Application.DisplayAlerts = False
Set newSheet = Sheets.Add(After:=ActiveSheet)
With newSheet
    On Error Resume Next
    ThisWorkbook.Sheets(sName).Range("A1").Address
    If Err = "" Then
        If MsgBox("Do you want to delete sheet " & sName, vbYesNo, "User Input") = vbYes Then
            ThisWorkbook.Sheets(sName).Delete
            .Name = sName
            On Error GoTo 0
        Else
            MsgBox "subTerminated"
            Exit Sub
        End If
    End If
End With
Application.DisplayAlerts = True

You could, of course simply remove

Application.DisplayAlerts = False

But the default Excel dialog box does not tell user the name of sheet about to be deleted - using own MessagerBox preferable

 

[yomomo]

Thanks! this works perfectly!