disabling a message box

[Guest]

I was wondering if anyone could help me with a macro I'm creating?

I'm trying to disable a message box "Do you want to save changes to
'Consolidated Programs import MONTHLY GL UPLOAD.txt" which pops up when my
macro is running. I want this to automatically default to yes each time the
macro is run. I also want the message box that says "'Consolidated Programs
import MONTHLY GL UPLOAD.txt already exists, do you want to replace it? To
default to yes as well.

Basically, I'm running a open workbook macro in one file to convert the
"Consolidated Programs import MONTHLY GL UPLOAD.xls" to a text file. The
message boxes are stopping the rest of my code from executing.

Any help would be appreciated.

Thanks,

Tim

 

[JE McGimpsey]

Wrap your Save/SaveAs method with

Application.DisplayAlerts = False
'Save
Application.DisplayAlerts = True

 

[Guest]

Thanks for your help JE McGimpsey!

It solved my first problem successfully. Do you, or anyone else know how to
help me with the second problem? I need to default the following message box
to yes:

A file named "'Consolidated Programs import MONTHLY GL UPLOAD.txt already
exists, do you want to replace it?

I'm going to be replacing the file under the same name each time I use the
macro.

Thanks again for your help!

 

[Guest]

Try this...

x = MsgBox("Default to yes", vbYesNoCancel + vbDefaultButton1, "Default")

Roy

 

[Guest]

Hi Roy,

Thanks for trying to help me out. Unfortunetly, the code you provided me
just created a seperate message box, and didn't default the original to yes.

 

[Tom Ogilvy]

If the message box is produced by code in the workbook you are opening, then
you can suppress that code from running

Application.EnableEvents = False
' open and process the workbook
Application.EnableEvents = True

 

[bhofsetz]

Tim,
Application.DisplayAlerts should take care of getting rid of both
of your unwanted message boxes. If you post your code and indicate
where the two message boxes are popping up then we may be able to
decipher the problem.

 

[Tom Ogilvy]

Guess I got lead astray by Roy's response. You are correct that
Application.DisplayAlerts should suppress the second message. However, that
was JE's original suggestion

Wrap your Save/SaveAs method with

Application.DisplayAlerts = False
'Save
Application.DisplayAlerts = True


and Tim claims it didn't. The first message wouldn't be produced by saving
the workbook - but that is the one he claimed it cured.

 

[Guest]

Thanks all for your help!

Here's a copy of my code: The code is opening another file and running a
macro and then saving it as a text file.


Private Sub Workbook_Open()

Application.ScreenUpdating = False

ChDir "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES"
Workbooks.Open Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\Consolidated
Programs import MONTHLY GL UPLOAD.xls"
Application.Run "'Consolidated Programs import MONTHLY GL
UPLOAD.xls'!Macro2"
ActiveWorkbook.SaveAs Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\Consolidated
Programs import MONTHLY GL UPLOAD.txt" _
, FileFormat:=xlText, CreateBackup:=False

Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True

Application.ScreenUpdating = False



UserForm1.Show False


With UserForm1.cmbprgimpt
.AddItem "54000"
.AddItem "54001"
.AddItem "54002"
.AddItem "54010"
.AddItem "54020"
.AddItem "54040"
.AddItem "Consolidated Programs import"

End With



End Sub

Any help would be appreciated.

 

[Tom Ogilvy]

Private Sub Workbook_Open()

Application.ScreenUpdating = False

ChDir "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES"
Workbooks.Open Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\Consolidated
Programs import MONTHLY GL UPLOAD.xls"
Application.Run "'Consolidated Programs import MONTHLY GL
UPLOAD.xls'!Macro2"

Application.DisplayAlerts = False



ActiveWorkbook.SaveAs Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\Consolidated
Programs import MONTHLY GL UPLOAD.txt" _
, FileFormat:=xlText, CreateBackup:=False

ActiveWindow.Close SaveChanges:=True
Application.DisplayAlerts = True

Application.ScreenUpdating = False



UserForm1.Show False


With UserForm1.cmbprgimpt
.AddItem "54000"
.AddItem "54001"
.AddItem "54002"
.AddItem "54010"
.AddItem "54020"
.AddItem "54040"
.AddItem "Consolidated Programs import"

End With



End Sub

 

[Guest]

Thanks for your help!
--
Regards,

timmulla

Private Sub Workbook_Open()

Application.ScreenUpdating = False

ChDir "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES"
Workbooks.Open Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\Consolidated
Programs import MONTHLY GL UPLOAD.xls"
Application.Run "'Consolidated Programs import MONTHLY GL
UPLOAD.xls'!Macro2"

Application.DisplayAlerts = False



ActiveWorkbook.SaveAs Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\Consolidated
Programs import MONTHLY GL UPLOAD.txt" _
, FileFormat:=xlText, CreateBackup:=False

ActiveWindow.Close SaveChanges:=True
Application.DisplayAlerts = True

Application.ScreenUpdating = False



UserForm1.Show False


With UserForm1.cmbprgimpt
.AddItem "54000"
.AddItem "54001"
.AddItem "54002"
.AddItem "54010"
.AddItem "54020"
.AddItem "54040"
.AddItem "Consolidated Programs import"

End With



End Sub