Macro to answer Alert question

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
Hi all -

I have a macro that will open a bunch of files, update the links, and save them. Occasionally a link can't be updated and an alert pops up. Is there a way in the macro to answer that pop-up? I want to code "Continue" to the Alert.

Any help would be most appreciated.

Amy
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
597
Office Version
2016
Platform
Windows
I have a macro
Can you paste your macro using the code tag [ CODE][ /CODE]
but without the spaces
so in short you want to ignore the error pop up? i believe the syntax you're looking for is
Code:
On Error Resume Next
but i can't tell you where to put it if i don't see the physical code.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
You can disable Excel alerts at the point they occur and reenable them after, for example:


Code:
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
 

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
Code:
    Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum680.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum620.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close

Can you paste your macro using the code tag [ CODE][ /CODE]
but without the spaces
so in short you want to ignore the error pop up? i believe the syntax you're looking for is
Code:
On Error Resume Next
but i can't tell you where to put it if i don't see the physical code.
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
597
Office Version
2016
Platform
Windows
using what john_w said your code should look like:


Code:
 Application.DisplayAlerts = False
 Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum680.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum620.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
Application.DisplayAlerts = True
or if you want to use error handling (*note i'm not sure if the alert counts as an error so this may not work):
Code:
On Error Resume Next
 Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum680.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
On Error Resume Next
    Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum620.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
or i imagine you could do both, the world is your oyster
 

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
You're right the On Error Resume Next didn't work. I guess the Alert box doesn't react to that. However, the Display Alerts = False did manage to skip the Alert box and continue with the macro. I know that that still means there may be broken links but baby steps.

Thanks so much for your help.

Amy

using what john_w said your code should look like:


Code:
 Application.DisplayAlerts = False
 Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum680.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum620.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
Application.DisplayAlerts = True
or if you want to use error handling (*note i'm not sure if the alert counts as an error so this may not work):
Code:
On Error Resume Next
 Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum680.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
On Error Resume Next
    Workbooks.Open Filename:= _
        "K:\Finance\Financial Strategy\Financial Analysis\Budget\2020\Expense Summaries\Sum620.XLS", _
        UpdateLinks:=3
    ActiveWorkbook.Save
    ActiveWindow.Close
or i imagine you could do both, the world is your oyster
 

MarieBocc

New Member
Joined
Jun 28, 2019
Messages
33
Hello everyone,

I have a question related to this one. I use a macro to create a new file which I save through the same macro that created it. I disable the DisplayAlert during the whole time this macro works and it all works the way I want it to.

But after closing this new workbook, when I try to reopen it, I get two consecutive pop-up Excel windows, the first one asking me if the source workbook is safe enough to run a reparation procedure and the second displaying a listbox of what has been repared.
The first window has two button, 'Yes' or 'No', and I always click on 'Yes'. The second is just a list and the only button I can click on is 'Close' which is what I do manually. This is repetitive and it happens everytime I open a file created through the said macro for the first time.

I was wondering if there was some vba functions I could use to automatically answer these two windows. Unlike the first macro, it looks like using the DisplayAlerts property would be kind of inefficient since the default value for the first window is 'No' when I want to select 'Yes', am I wrong ?
Also I don't know if I can run a macro for this at all since I think it happens before the worksheets finish charging but I am new to vba so I might very well be mistaking.

Do you have any ideas ?

Thank you for reading,

Marie
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top