Pop up Windows Question

[Elton Law]

Dear Expert,

I have written some macros.
Sometimes, it has "save" function.
Then it would pop up a MSG windows asking you "Do you want to save changes
you made to 'MMDEALS.XLS'?

Macro will stop until you click yes or now.

Can VB scripts be written to answer "Yes" automatically ?

Also, If I use "save as", there is a pop up msgbox too.
A file named 'C:\marco\Income_new.xls' already exists in this location. Do
you want to replace it?

Can VB scripts be written to answer "Yes" automatically ?

Thanks so much
Elton

 

[Jacob Skaria]

Please refer..Cant we take off the Msgbox and Save by default...

Another way is to Save the file in all cases of vbYes/vbNo/vbCancel

OR

You can also try SENDKEYS() . Refer the below link
http://www.developerfusion.com/code/141/close-a-message-box-automatically/

Please try and let us know

If this post helps click Yes

 

[Dave Peterson]

Just tell excel how to close the workbook.

Dim wkbk as workbook
set wkbk = .....
.....
wkbk.close savechanges:=true 'or false to discard the changes.

 

[Elton Law]

Hi Dave,
Please apologize if I am stupid.
I try to copy your wordings and test.
I does not work
It stops at "wkbk.Close Savechanges:=True"
Is it due to my version ?
Mine is Excel 2000 only.
Thanks
Elton

Sub Macro1()

Dim wkbk As Workbook
Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

wkbk.Close Savechanges:=True

End Sub

 

[Charlotte E]

If you want Excel to always SAVE, why ask in the first place?!?

But, if you really want the MsgBox, you could do something like:

Answer = MsgBox("Save?",vbYesNo,"Save")
Answer = vbYes

....continue saving here...

 

[Jacob Skaria]

Try this code

Dim wkbk As Workbook
Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test.xls"
wkbk.Close Savechanges:=True
Application.DisplayAlerts = True
End Sub

 

[Jacob Skaria]

Please note that I have only modified the last part of your code.....

If this post helps click Yes

 

[Elton Law]

Hi Super Jacob,
Really appreciate your help today ....

I try run as what you say ....

It stops at here - "wkbk.Close Savechanges:=True"
Pop up a msgbox saying Run-time error '91'
Object variable or with block variable not set

Then what should I do ? Thanks
Elton

(quote)
Sub Marco2()
Dim wkbk As Workbook

Windows("CXLFX.xls").Activate
Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test111.xls"
wkbk.Close Savechanges:=True
Application.DisplayAlerts = True
End Sub
(End of Quote)

 

[Jacob Skaria]

Oops....Replace error line with...As I mentioned earlier I havent dont much
changes to the code....

ActiveWorkbook.Close Savechanges:=True