Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
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 On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
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 "Gord" wrote in message ... 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 On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C wrote: 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! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
Thank you very much - I used the second method and it worked perfectly! Thanks! On Aug 16, 5:59*pm, Gord wrote: 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 On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C wrote: 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!- Hide quoted text - - Show quoted text - T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
Cliff
OP stated he wanted to close the source workbook and say NO to saving. 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. Just complying with his wishes. Gord On Tue, 16 Aug 2011 17:15:03 -0500, "Clif McIrvin" wrote: 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 "Gord" wrote in message .. . 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 On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saying No to Message Box prompt through VBA
Gord has brought this to us :
OP stated he wanted to close the source workbook and say NO to saving. 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. 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Message Box Prompt | Excel Programming | |||
help with message prompt | New Users to Excel | |||
Message prompt in VBA | Excel Programming | |||
Disable Message Prompt in VBA | Excel Programming | |||
Deleting a Prompt Message | Excel Programming |