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.

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.

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.

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.