VBA Save and Clear before exiting

[bloodmilksky]

Hi Guys, I hope everyone is good today

I was just wondering if anyone knew the VBA syntax a Sub that would ask the user if they have finished using a message box then if yes clear a range of cells say A1:D4 then save and close the spreadsheet and if no allow them to continue.

any help is as always is greatly appreciated

Many thanks

 

[Jaafar Tribak]

What do you mean by "they have finished using a message box" . Maybe you can elaborate a little bit on that

 

[bloodmilksky]

sorry that should have been using a message box ask them if they have finished? if yes clear a range of cells say A1:D4 then save and close the spreadsheet and if no allow them to continue.

 

[michaelsmith559]

Try this:

Sub Finish_and_Clear()
Dim answer As Integer
answer = MsgBox("Are you through working?", vbYesNo + vbQuestion, "Clear Range")

If answer = vbYes Then
    Range("A1:D4").ClearContents
Else
    Exit Sub
End If

End Sub

 

[bloodmilksky]

will that save the workbook as well?

 

[Tim_Excel_]

Sub Save_file()
Dim mbox As Integer
mbox = MsgBox("Save file?", vbYesNo, "Save file")
If mbox = vbYes Then
Range("A1:D4").Clear
ActiveWorkbook.SaveAs Filename:="pathfile.extention", FileFormat:=[COLOR="#FF0000"]xlWorkbookNormal[/COLOR]
mbox= MsgBox("File saved", vbOKOnly, "Saved")
Workbooks(1).Close SaveChanges:=False 'change the name of the workbook if necessary
Else
mbox = MsgBox("File not saved", vbOKOnly, "Not saved")
End If
End sub

try this for saving the file