VBA to prevent pop up of File XYZ exists

emiguy

New Member
Joined
Feb 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi everyone.
I asked part one of this question and it was answered quickly. HOW CAN I FIND THAT THREAD SO I DON'T DUPLICATE IN THE FUTURE?? (uggh....sorry)

Goal: I have a 365 macro to save the file as a XLS file, then a PDF. Someone (thank you to that person!!) answered the question, but now that edit in my code (see below and image) causes a WITH error.

Also, I'm not a VBA'er -But- am learning the power of writing VBA ==> What resources are good? Something with "Heres an example of using VBA to....." instead of "Here are some commands to learn."

Here's the full code
VBA Code:
//-----------------------   BEGIN VBA
'============   MYMACRO

Sub AA_PW_Changer_on_its_file_021121()

' PW changer saver as PD fand xls  02-11-21  1230  XLS kept dpoing "autorecovered file" in macro   ??
' A_save_PW_as_xls_and_pdf Macro
' turn off application alerts
'
'==========  TURN OFF APPLICATION ALERTS
'
With Application.DisplayAlerts = False              'Turns off alerts
AlertBeforeOverwriting = False          'Turns of overwrite
Alerts.ScreenUpdating = False                   'Turns of screen updating
End With
' ------------------------------------------------------------------------------------------
    ActiveWorkbook.SaveAs Filename:= _
        "E:\Dropbox\G_Drive_PA_HH\EXCEL\password changer.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "E:\Dropbox\G_Drive_PA_HH\EXCEL\password changer.pdf", Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
    ActiveWorkbook.SaveAs Filename:= _
        "E:\Dropbox\G_Drive_PA_HH\EXCEL\password changer.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True
    ActiveWindow.SmallScroll Down:=-75
    Range("F3:J7").Select
' ------------------------------------------------------------------------------------------
'==========  TURN  ==> on <== APPLICATION ALERTS
'
With Application.DisplayAlerts = True                 'Turns back on alerts
AlertBeforeOverwriting = True        'Turns on Overwrite alerts
ScreenUpdating = True                'Turns on screen updating

End With
End Sub
//-----------------------    END vba


Thanks !!
Kirk
USA , Pennsylvania
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
448
Office Version
  1. 2016
Platform
  1. Windows
Start with fixing your "Application Alerts" disable commands:
VBA Code:
With Application
    .DisplayAlerts = False                    'Turns off alerts
    .AlertBeforeOverwriting = False           'Turns of overwrite
    .ScreenUpdating = False                   'Turns of screen updating
End With
and their respective enable commands at the end of the macro.
 

Forum statistics

Threads
1,136,274
Messages
5,674,772
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top