VBA Close Window

[MDuff]

Hi all I have been Playing with this simple code And I had a Question Every time the code runs with the part in bold it asks me if I want to save the Changes to the workbook (this work book has links) Is there I way I can have excel say yes or know to this with out having to click on it
Any help is very appreciated

private Sub Workbook_Open()
ActiveSheet.Unprotect Password:="12345"

Workbooks.Open Filename:= _
"\\Crsjofil001\Accounts\GEA\Warranty Management\HR Documents\Performance Appraisals\Performance Appraisals - 2nd Semester 2003\GEA Performance Appraisals Warranty Management II 2003 xls.xls"

ActiveWindow.Close
Windows("PA Summary WM.xls").Activate


ActiveSheet.Protect Password:="12345", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

thanks Mike

 

[WillR]

ActiveWindow.Close True

(to save changes)
or

ActiveWindow.Close  False

to close without saving changes

Hope this helps

 

[MDuff]

Thanks Worked Like a Charm ray:

WillR

 

[MDuff]

Hi all I have an other question about this code is there a way to tell excel that if the file is already open not to run the code? what happens if a person has GEA Performance Appraisals Warranty Management II 2003 xls.xls already open it will give a message that the file is already open etc and then give a error with the code.

thanks in advance

 

[Huby00]

Hello,

I need to close a window but i get a popup saying "There is a large amount of information on the clipboard. do you want to be able to paste this information into another program later?"

Is there anyway i can add some code to close this message box as no after my activewindow.close false? or will it not work because its a OS based pop up message?

Regards,
Hub

 

[John Davis]

Hello,

I need to close a window but i get a popup saying "There is a large amount of information on the clipboard. do you want to be able to paste this information into another program later?"

Is there anyway i can add some code to close this message box as no after my activewindow.close false? or will it not work because its a OS based pop up message?

Regards,
Hub

Maybe:

Application.DisplayAlerts = False

Your Code

Application.CutCopyMode = False
Application.DisplayAlerts = True

 

[Huby00]

Mr. Davis, you are a sexy man.

That worked beautifully. Thank you for your repsonse and help.

 

[John Davis]

Mr. Davis, you are a sexy man.

That worked beautifully. Thank you for your repsonse and help.

You're very welcome. Glad too help, and thanks for the feedback.

 

[misiorto]

Hi,
I have a different quite serioues and I guess difficult probelm with ActiveWindows.Close or ActiveWorkbook.Close.

I have a macro opening files in a given folder (sorry for Polish nems of the variables, hope this won't bother you):

Sub odpal_dir()
sciezka = ThisWorkbook.Path & "\Do przeliczenia\"
nazwa_pliku = Dir(sciezka)

Do While nazwa_pliku <> ""
Set odczytany = Workbooks.Open(sciezka & nazwa_pliku)
odczytany.Sheets("Data").Select
Calculate
Application.Run "'" & nazwa_pliku & "'!CashFlow"
nazwa_pliku = Dir
ThisWorkbook.Activate
Loop
End Sub

As you can see this macro triggers macro CashFlow defined in the files opend by macro odpal_dir (each file contains macro cashflow). Now let's have a look on this internal macro:

Sub cashflow()
'Code (irrelevant)
ActiveWorkbook.Close SaveChanges:=True 'or ActiveWindows.Close
End Sub

Unfortunately, after opening the first file and running the internal macro "cashflow" the code interrupts and the other files are not opened. It seems as if closing window/workbook causes excel to lose track of the external code.
I know one possible solution: I can remove line: "ActiveWorkbook.Close SaveChanges:=True" from the internal macros "cashflow" and transfer it to the external macro "odpal_dir". However, it would be convenient for me to have the line in the internal macros, as the internal macros are sometimes run separately and than I would like the internal macro to save and close the file after code execution.
Any possible solution to my problem. I guess it might be unsolvable - if you think so please let me know so I can give up and wase no more time.

BR,
misiorto

 

[John Davis]

From what I see, the Cash Flow macro would stop execution when it closes the Active workbook. Why not just save the changes instead?