Auto clicking on a message box using VBA

Question

I am automating testcases for my company's addin.

In one of the testcases, after saving my workbook on my local machine when I try closing the workbook, I am required to click on 'Yes'- saves edited data in our database, 'No'- discards the edits or 'cancel'- simply closes the message box.

I have tried using sendKeys but that didn't work for me.

Eg: For clicking 'No' through my VBA code, I tried to send tab and enter keys like this:

Application.Wait (Now() + TimeValue("00:00:10"))
Call SendKeys("{TAB}", True)
Call SendKeys("{ENTER}", True)

I have tried with various time values still didn't get any success. I would be glad if someone can help me with this.

Thanks in advance! :)

UPDATE: As of now, I have been doing this:

'Some tasks...
ActiveWorkbook.Save
Call Application.Run("workbook.xlsm!Discard")
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

But is there any way to get the window object reference for the message box? Or anyway to stick to the workflow: edit workbook -> save -> try closing -> opt to discard/commit changes to database?

NOTE: By discarding/committing changes to database, what I mean is a server where the data gets updated. Excel is just an interface for the user. I don't want just the excel to be saved but the operation of calling discard/save changes on server as well...

Answer 1

if you have already saved the workbook, you only need to close the workbook. Please try the below code

Activeworkbook.close false

This will close your workbook and you wont be promted for selecting any options.