Doesnt like vbCancel button

[thomas.szwed]

Hello,

Having a small problem with my cancel button in the following code....When i press cancel it still performs the vbOK action for some reason when it should be exiting the Sub....can anyone see where im going wrong??

Thanks a lot


Sub CreateArchive()
Sheets("For Archive").Activate
Dim msgResponse As String 'confirm delete
Application.ScreenUpdating = False

'get user confirmation
msgResponse = MsgBox("This will archive records from the 'For Archive' Sheet. This action is irreversible. Continue?", _
vbCritical + vbYesNo, "Archive Records")

Select Case msgResponse 'action dependent on response

Case vbYes

'Input box to name new file
NewName = InputBox("Enter Month for filename", "Archiving Records", vbOKCancel)

If vbOK Then

'Save it with the NewName and in the same directory as original
Worksheets("For Archive").Copy
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False
Worksheets("For Archive").Range("A2:AC65000").ClearContents

Else

Exit Sub
End If

Case vbNo

Exit Sub

End Select

End Sub

[Bob Phillips]

You have been told this on at least TWO occasions.

Inputbox doesn't return the button that you pressed, it returns the value input. MsgBox returns the button.

[RECrerar]

Change


If vbOK Then

to


If newname <> vbnullstring

or something similar

[thomas.szwed]

Thanks this solution works for me.....Tom...the others told me what was wrong but i didnt really know how to correct it.

[Bob Phillips]

I have no idea who Tom is, but we told you how to do it as well. If you hadn't been told, you should have asked, persevering with a failed methodology is bordering on the asinine.