asked on

Kill Excel Dialog Box (if it exists)

I am opening a file from a network drive, entering some data, then saving it with changes.

Randomly, the network will hang and a "uploading file to server...." dialog box will appear with a "cancel" button. What I want is if that box appears, autoclick the cancel button. The following code works, however, if that dialog box does not appear, sending the keystroke causes the next dialog box to be clicked through. I'd like to find a way to detect if the dialog box is there and only send the keystroke if it is.

Windows("MyFile.xlsx").Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
Application.SendKeys ("~") ' this works fine unless the dialog box doesn't appear, then it clicks through the next dialog box.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayAlerts = True
MsgBox ("Report Complete")

Professor J

Set this line to false

Application.DisplayAlerts = True

swjtx99

ASKER

Hi Professorjimjam,

Thanks for the reply. That didn't work. I need someway to not send that keystroke unless a dialog box is open or cancel the keystroke before it gets to the next dialog box.

Thanks

Professor J

Can you try this

indows("MyFile.xlsx").Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.SendKeys ("~")
MsgBox ("Report Complete")
ActiveWorkbook.Close savechanges:=True
' this works fine unless
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayAlerts = True

swjtx99

ASKER

Hi Professorjimjam,

Thanks for your reply. Didn't work. It just clicks through the "report complete" dialog box and if the other one opens, it is displayed with a cancel button.

Professor J

I don't have your full code , why would you need to use the application send tilde that is used to press enter . Can you remove that line and try I mean the application send key

swjtx99

ASKER

Hi ProfessorJimJam,

Let me explain. I am saving a file with changes to a network drive. Sometimes this "hangs up" and I get the dialog box. When that happens, I just want to automatically have the code click the default button (which is "cancel"). I don't want to send the tilde unless the dialog box is displayed.

If I remove that line and the "save" hangs up, I end up with a dialog box that says ""uploading file to server....".

swjtx99

ASKER

Also, if I do not remove the line and it doesn't hang up, the keystroke will click through the next dialog box which I don't want to have happen.

Professor J

Can try with putting this code on the "Thisworkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved
End sub

swjtx99

ASKER

Another way to do this might be to only give the ActiveWorkbook.Close savechanges:=True 1 second to execute. If it takes longer that means the dialog box is open and the keystroke needs to be sent. if it finishes in under 1 sec, no keystroke is sent.

Maybe do it with a time reference? Set a variable that is now and if the next line takes longer than Now + 1 second, send the keystroke? No idea if that is feasible.

Grasping at straws here :-)

Professor J

is it possible to upload a dummy file with the complete code. so that i look where the problem is?

i feel like i did not understand the question/problem. can you elabore and if possible upload the screenshot?

from what i see that you are trying to save multiple workbooks and if one of them get stuck then you cancel and move to another correct?

if that is the case, instead of activating workbook and saving it, you could use something simpler like this

Sub SaveAllwbks()
    Dim Wkb As Workbook
    For Each Wkb In Workbooks
        If Not Wkb.ReadOnly And Windows(Wkb.Name).Visible Then
            Wkb.Save
        End If
    Next
End Sub

Open in new window

swjtx99

ASKER

Hi Professorjimjam,

I am opening just one file on a server, making an entry, then saving it with changes.

Sometimes that works with no issues. Sometimes it does not and a dialog box appears saying the file is uploading to the server. When that occurs, I want to autoclick the "Cancel" button.

Thanks,

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

swjtx99

ASKER

My apologies. Nothing worked.