Overwrite msgbox Yes/No issue

[Jonseyt23]

Hello

I am in need of your help, I am trying to add a message box popup in VBA that when a user selects file>save or ctrl+s, it prompts them with a message box that states "Do you really want to overwrite this file?" Yes button to continue to save, No button to cancel and close the message box.

I tried using the below code but pressing no still saves it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Do you really want to overwrite this file?", vbYesNo
End Sub

I am not very clued up on VBA so I might be missing something simple, but cant figure it out.

Best Regards

Dave

 

[FormR]

Hi, welcome to the forum!

You need to test what the result of the msgbox is and cancel the save if required - for example:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If MsgBox("Do you really want to overwrite this file?", vbYesNo) = vbNo Then Cancel
End Sub

 

[Jonseyt23]

Hello, thank you for you're reply, the result of the current msgbox string is, box come up, pressing Yes saves it, pressing No also saves it.

I have tried adding the
vbYesNo) = vbNo Then Cancel, like in the example. but now getting a Compile error - Expected sub, Function or Property within the VBA editor

 

[FormR]

now getting a Compile error

Sorry, it should be..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If MsgBox("Do you really want to overwrite this file?", vbYesNo) = vbNo Then Cancel = True
End Sub

 

[Jonseyt23]

Excellent FormR, yr a star

 

[Jonseyt23]

Just a little addition to this msg box if it can be done, how can I add the vbExclamation to give it a bit more of a visual appearance.

 

[FormR]

Hi, you can try like this.:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If MsgBox("Do you really want to overwrite this file?", vbYesNo + vbExclamation) = vbNo Then Cancel = True
End Sub

 

[Jonseyt23]

Thank you, works a treat