MsgBox Not working

[sassriverrat]

When I run this piece- the msgbox response doesn't seem to matter- it to delete the sheet and then create a new one whether I hit 'yes, 'no' or cancel.

Note: the VoyageSpecificsCreate is a HUGE formatting piece- just for the application pieces there.

Sub VoyageSpecifics()' VoyageSpecifics Macro
' This controls a page that has all of the Voyage Specifics saved on it
'Speeds up the formating part
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
'Error Checks for a Voyage Specifics Sheet already existing
    If SheetExists("Voyage Specifics") Then
        MsgBox "Voyage Specifics Sheet already exists, would you like to delete it and start over?", vbYesNoCancel
        If vbYes Then
            Sheets("Voyage Specifics").Delete
            Call VoyageSpecificsCreate
        ElseIf vbNo Then
            Sheets("VoyageSpecifics").Select
            Exit Sub
        End If
    Else: Call VoyageSpecificsCreate
    End If
    'Speeds up the formating part
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[sassriverrat]

The msgbox doesn't seem to be working here. If I hit 'no or 'cancel it still wants to delete the sheet and then create a new one. Did I write this piece incorrectly?

Also for the delete piece- it asks, per msgbox, if I want to delete the sheet. Then excel asks if I want to delete because it will be permanent- is there a way to remove that extra dialog box and just execute the delete?

Thanks for the help

Sub VoyageSpecifics()' VoyageSpecifics Macro
' This controls a page that has all of the Voyage Specifics saved on it
'Speeds up the formating part
'Error Checks for a Voyage Specifics Sheet already existing
    If SheetExists("Voyage Specifics") Then
        MsgBox "Voyage Specifics Sheet already exists, would you like to delete it and start over?", vbYesNoCancel
        If vbYes Then
            Sheets("Voyage Specifics").Delete
            Call VoyageSpecificsCreate
        ElseIf vbNo Then
            Sheets("VoyageSpecifics").Select
            Exit Sub
        End If
    Else: Call VoyageSpecificsCreate
    End If
    'Speeds up the formating part
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Tim_Excel_]

Dim the messagebox as a variable and it will work

MB = MsgBox("Voyage Specifics Sheet already exists, would you like to delete it and start over?", vbYesNoCancel)
If MB = vbYes Then
    'code
ElseIf MB = vbNo Then
    'code
End If

For the popup problem,

[COLOR=#333333]Application.DisplayAlerts = False[/COLOR]

Put this line before closing the workbook

 

[Rick Rothstein]

You have to assign the output from the MsgBox function to a variable and then test that variable against vbYes and vbNo...

[table="width: 500"]
[tr]
	[td]        Answer = MsgBox("Voyage Specifics Sheet already exists, would you like to delete it and start over?", vbYesNoCancel)
        If Answer = vbYes Then
            Sheets("Voyage Specifics").Delete
            Call VoyageSpecificsCreate
        ElseIf Answer = vbNo Then
            Sheets("VoyageSpecifics").Select
            Exit Sub
        End If[/td]
[/tr]
[/table]

 

[nemmi69]

Try this , it captures the response as an integer

Sub xccfrf()
Dim Rsp As Integer
       Rsp = MsgBox("Voyage Specifics Sheet already exists, would you like to delete it and start over?", vbYesNoCancel)
        If Rsp = 6 Then
            MsgBox "YES  - " & Rsp
        ElseIf Rsp = 7 Then
            MsgBox "NO"
        Else
        MsgBox "Rsp " & Rsp
        End If
End Sub

 

[sassriverrat]

Worked Perfectly! Thank you sir!

 

[Tim_Excel_]

Glad to have helped

 

[Fluff]

@sassriverrat
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).
I have merged both threads.