VBOKCancel issues

[sassy]

Hi, I need a little help from the experts!!! I have the following code and the cancel button is not working. It still runs the rest of the SUB. Is there a way, without using an IF statement to make the Cancel button on the message box work? Also, if i have to use an IF statement, do I have to change the DIM statement?

thanks!!!

Sub Clear_Sheet()
'This will clear the data entry sheet only. only has cell that are completed as of 1/17/2011
Dim Response As VbMsgBoxResult
'
Response = MsgBox("Are you sure you want to clear the sheet?", vbOKCancel, "Clear Sheet?")
Worksheets("Data Entry").Select
ActiveSheet.Unprotect
'
Range("I92,D2:E2,D4:E4,D6:E6,D8:E8,D10:E10,H18,B20:H20,H24,B26:H26").Select
Range("B26").Activate
Selection.ClearContents

 

[dave3009]

You probably need to just assess Response

something like

If Response = vbCancel Then Exit Sub

Should stop the rest of the code being executed.

 

[Ruddles]

Response = MsgBox("Are you sure you want to clear the sheet?", vbOKCancel, "Clear Sheet?")

All this command does is get the user's response and store it in Response. As Dave says, you need to test what the user's response actually is otherwise VBA can't know what to do. I mean, it doesn't know whether you asked "Are you sure you want to clear the sheet?" or "Shall I leave the contents of the sheet intact?".

So no, you do need to use an IF to test the response and take the appropriate action, exactly as Dave has specified.

 

[Rasm]

insert breakpoint on code line after response - when code pauses - put cursor on responce - you can see value - will be an integer. If you dont know how to add breakpoint then insert

STOP

after response line - now place cursor on response - this is a bad habbit