Help with MSG Box

[Guest]

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 Deitrick]

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

 

[Paul B]

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

 

[Guest]

Bernie, Paul

Thanks for the prompt response and help. Both options as expected run a
treat.

Have a good weekend.