MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MsgBox - Change Default Button


Posted by JAF on April 20, 2001 2:26 AM

Hi

I have a piece of code that uses a Message Box to obtain user clarification that they wish to run a certain macro (code below) that clears out data on several worksheets.

Sub Clear_ALL()
msg = ""
msg = msg & "Running this macro will clear ALL data from the Input & Scottish" & vbCrLf
msg = msg & "sheets and all Shortfall Repaid cases from the Shortfall sheet." & vbCrLf & vbCrLf
msg = msg & "Are you CERTAIN you want to continue???"
ans = MsgBox(msg, vbCritical + vbYesNo, "Are you SURE?????")
Select Case ans
Case vbYes
MsgBox "run code"
Case vbNo
MsgBox "Do NOT run code"
End Select
End Sub

Using this method, the "Yes" button on the Message Box is the default button and I want the "No" button to be the default because I don't want a user to press the return button and accidentally accept the default which would clear out all data.

I realise that I could rephrase the question so that selecting the default of "Yes" leaves the data intact or that I could create my own User Form to ask the same question, but I like the convenience of MsgBox.

So, is there any way to change the default button on the Message Box created above from "Yes" to "No"??

Over to you.....

JAF


Posted by Dave Hawley on April 20, 2001 2:58 AM

Hi JAF

The Msgbox function also takes another argument that allows you to define your default button, see below:


Sub Clear_ALL()
msg = ""
msg = msg & "Running this macro will clear ALL data from the Input & Scottish" & vbCrLf
msg = msg & "sheets and all Shortfall Repaid cases from the Shortfall sheet." & vbCrLf & vbCrLf
msg = msg & "Are you CERTAIN you want to continue???"
ans = MsgBox(msg, vbCritical + vbYesNo + vbDefaultButton2, "Are you SURE?????")
Select Case ans
Case vbYes
MsgBox "run code"
Case vbNo
MsgBox "Do NOT run code"
End Select
End Sub


Dave

OzGrid Business Applications

Posted by JAF on April 20, 2001 3:32 AM

Excellent - knew there had to be a way!!

Thanks again Dave!!