Adding Warning Messages Within A Macro

[RJO211980]

Hi - I have written a very simple macro that just clears the contents of a sheet from cell A5 to AC5 all the way down to cell A1000 to AC1000. However, since creating the macro, i have realised that it would probably be beneficial to put a warning box within the macro to ask the user, if they are sure they want to clear the contents of sheet.

I am new to VBA code and although i have found various website showing me how to create message boxes I am not confident in where that particularr code should be placed and if i need to change any of the drop down options within the VBA editor for the afroementioned drop down box.

As the data on the sheet is important and obviously with one click it could all be rid off, i need to safeguard the deletion of this data.

All the warning message really needs to say is "Are you sure you want to delete the data within this sheet? with a 'Yes' or 'No' as the two options provided.

Perhaps followed by another message box requesting that the user then save the new workbook in a pre-determined location with a different file name.

Any ideas are gratefully accepted.

Many thanks
Rich

 

[onlyadrafter]

Hello,

something like this?

'your code
    MY_CHOICE = MsgBox("Are you sure?", vbYesNo, "CONFIRMATION")
    If MY_CHOICE = vbNo Then Exit Sub
'rest of your code

 

[VoG]

Try something like

Dim Ans As VbMsgBoxResult
Ans = MsgBox("Are you sure you want to delete the data within this sheet?", vbYesNo + vbQuestion)
If Ans = vbNo Then Exit Sub

 

[RJO211980]

Thanks for the amazingly quick replies! Much Appreciated. Hopefully not to make myself sound stupid now, but my knowledge of VBA is very basic, where you write:
'your code
.........
'rest of your code

Does this mean (and i hope that i answer my own question) it all goes at the beginning, as the macro runs in order?

 

[VoG]

Like this

Dim Ans As VbMsgBoxResult
Ans = MsgBox("Are you sure you want to delete the data within this sheet?", vbYesNo + vbQuestion)
If Ans = vbNo Then Exit Sub
'
'code to clear the worksheet goes here
'

 

[RJO211980]

That works perfectly, many thanks.

If i wanted to create secondary box to that one that read:

Please remember to save the new document in P:\Customers\RFHSCU\Delivered items in the corresponding months folder",

With the options of OK or Cancel, where Cancel would cancel the Macro, how would i write this please

Many Thanks
Rich

 

[VoG]

Try

Dim Ans1 As VbMsgBoxResult, Ans2 As VbMsgBoxResult
Ans1 = MsgBox("Are you sure you want to delete the data within this sheet?", vbYesNo + vbQuestion)
If Ans1 = vbNo Then Exit Sub
Ans2 = MsgBox("Please remember to save the new document in P:\Customers\RFHSCU\Delivered items in the corresponding months folder", vbOKCancel + vbInformation)
If Ans2 = vbCancel Then Exit Sub
'
'code to clear the worksheet goes here
'

 

[RJO211980]

Thanks very much, you have been very helpful

Regards
Rich