Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with MSG Box
I have a macro that will clear out data from a sheet. To ensure that the
sheet is not cleared out in error I want to use a message box to ask the question €˜Are you sure that you want to clear out the data?. If the answer is Yes I want the macro to run, if the answer is No, then nothing should be done. Additionally, is there a way to display the selected sheet name within the message box? (i.e. You have selected <sheet name to be cleared out, are you sure?). Any help would be appreciated. |
#2
|
|||
|
|||
Pank,
If MsgBox("You have selected " _ & ActiveSheet.Name & " to be cleared out, are you sure?", _ vbYesNo) = vbYes Then MsgBox "I'm going to clear it now!" 'You code to clear sheet here Else MsgBox "Good thing you changed your mind!" End If HTH, Bernie MS Excel MVP "Pank Mehta" wrote in message ... I have a macro that will clear out data from a sheet. To ensure that the sheet is not cleared out in error I want to use a message box to ask the question 'Are you sure that you want to clear out the data?'. If the answer is Yes I want the macro to run, if the answer is No, then nothing should be done. Additionally, is there a way to display the selected sheet name within the message box? (i.e. You have selected <sheet name to be cleared out, are you sure?). Any help would be appreciated. |
#3
|
|||
|
|||
Pank, here is one way,
Sub clear_range() Msg = "You have selected sheet " & _ ActiveSheet.Name & _ " to clear out, do you want to clear this sheet ?" Title = "Continue ?" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) If Response = vbNo Then Exit Sub ' Quit the macro End If 'change to your range ActiveSheet.Range("A1:A10").ClearContents End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Pank Mehta" wrote in message ... I have a macro that will clear out data from a sheet. To ensure that the sheet is not cleared out in error I want to use a message box to ask the question 'Are you sure that you want to clear out the data?'. If the answer is Yes I want the macro to run, if the answer is No, then nothing should be done. Additionally, is there a way to display the selected sheet name within the message box? (i.e. You have selected <sheet name to be cleared out, are you sure?). Any help would be appreciated. |
#4
|
|||
|
|||
Bernie, Paul
Thanks for the prompt response and help. Both options as expected run a treat. Have a good weekend. "Paul B" wrote: Pank, here is one way, Sub clear_range() Msg = "You have selected sheet " & _ ActiveSheet.Name & _ " to clear out, do you want to clear this sheet ?" Title = "Continue ?" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) If Response = vbNo Then Exit Sub ' Quit the macro End If 'change to your range ActiveSheet.Range("A1:A10").ClearContents End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Pank Mehta" wrote in message ... I have a macro that will clear out data from a sheet. To ensure that the sheet is not cleared out in error I want to use a message box to ask the question 'Are you sure that you want to clear out the data?'. If the answer is Yes I want the macro to run, if the answer is No, then nothing should be done. Additionally, is there a way to display the selected sheet name within the message box? (i.e. You have selected <sheet name to be cleared out, are you sure?). Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|