Saying No to Message Box prompt through VBA

[Haas C]

Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!

 

[Gord]

Couple of methods.

1...............In Thisworkbook module of source workbook to close
without saving.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

2................In macro which closes the source workbook

Application.displayalerts = false

close the workbook

Application.displayalerts = true


Gord Dibben Microsoft Excel MVP

 

[Clif McIrvin]

Just a comment on Gord's option 1: With this code, if you open the
workbook manually, enter any changes and close the workbook (without
doing a manual save) the workbook will obediently close without saving
the changes.

Clif

 

[Haas C]

Thank you very much - I used the second method and it worked
perfectly! Thanks!

Couple of methods.

1...............In Thisworkbook module of source workbook to close
without saving.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
          ThisWorkbook.Saved = True
End Sub

2................In macro which closes the source workbook

Application.displayalerts = false

         close the workbook

Application.displayalerts = true

Gord Dibben    Microsoft Excel MVP





- Show quoted text -

T

 

[GS]

Haas C formulated on Tuesday :

Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!

Workbooks("NameGoesHere.xls").Close SaveChanges:=False

 

[Gord]

Cliff

OP stated he wanted to close the source workbook and say NO to saving.

Just complying with his wishes.


Gord

 

[GS]

Gord has brought this to us :

OP stated he wanted to close the source workbook and say NO to saving.


Just complying with his wishes.


Gord

So why not use the SaveChanges arg for the Close method? Surely this
would be preferable over adding code to the xls so it fires a security
warning when opened. Also, it occupies the same line of code, obviating
the need to turn DisplayAlerts off/on and so saves the extra
processing.