If then statement if user chooses cancel button when Windows asks if ok to open file

[sdoppke]

For the life of me i cannot figure out how to do this simple command...

I have a script that opens a file from a web path. When the script runs, Windows shoots a popup that asks if its ok to open the file. Id like it not to error out if the user chooses "cancel" not to open the file.

so:
If user chooses to open file, then

If user chooses cancel then

Thanks all you brainiacs

sd

 

[pbornemeier]

This type of code can take care of that:

    sFilePathName = Application.GetOpenFilename("All Files (*.doc*), *.doc*")
    If sFilePathName = "" Then
        MsgBox "No file selected.  Cannot continue."
        GoTo End_Sub:
    End If

 

[Ruddles]

It's a bit tricky suggesting how you could modify your script without being able to see it...

 

[sdoppke]

It's a bit tricky suggesting how you could modify your script without being able to see it...

Indeed you are right:

Sub Open_VCR_Report()
ActiveWorkbook.FollowHyperlink Address:="[URL]http://blablabla.zip[/URL]"
MsgBox "Make sure VCR report is fully opened, then go back and CLICK Import Lastest VCR Report.", vbInformation, "Open VCR Report"
End Sub

Thank agian

sd

 

[sdoppke]

This type of code can take care of that:

    sFilePathName = Application.GetOpenFilename("All Files (*.doc*), *.doc*")
    If sFilePathName = "" Then
        MsgBox "No file selected.  Cannot continue."
        GoTo End_Sub:
    End If

Thank you very much for your response, i tried like this:

Sub Open_VCR_Report()
ActiveWorkbook.FollowHyperlink Address:="[URL="http://infonet.t-mobile.com/sites/retail/Retail%20Reporting%20Portal/Store%20Manager/White%20Board%20Reports/Value%20Contribution%20Report%20(Daily)%20(Excel).zip"]blablabla.zip[/URL]"
 
    sFilePathName = Application.GetOpenFilename("All Files (*.doc*), *.doc*")
    If sFilePathName = "" Then
        MsgBox "No file selected.  Cannot continue."
        GoTo End_Sub:
    End If
    
MsgBox "Make sure VCR report is fully opened, then go back and CLICK Import Lastest VCR Report.", vbInformation, "Open VCR Report"
End Sub

and got a compile error: label not defined.

Any ideas where i might be going wrong?

sd

 

[Ruddles]

Please describe the pop-up message. Is it from Microsoft Office: "Some files can contain viruses..."? Or are you referring to the File Download dialog box?

And what error report do you get? Run time error 287?

 

[sdoppke]

Please describe the pop-up message. Is it from Microsoft Office: "Some files can contain viruses..."? Or are you referring to the File Download dialog box?

And what error report do you get? Run time error 287?

Yep its just MS asking if want to open, (its my file) After choosing cancel, is a runtime error of 287

sd

 

[Ruddles]

<deleted>

 

[Ruddles]

Try this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Sub Open_VCR_Report()[/FONT]
 
[FONT=Fixedsys]  On Error Resume Next[/FONT]
 
[FONT=Fixedsys]  ActiveWorkbook.FollowHyperlink Address:="blahblah.zip"[/FONT]
 
[FONT=Fixedsys]  If Err.Number = 287 Then[/FONT][FONT=Fixedsys][COLOR=green] ' user clicked Cancel[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=magenta]    MsgBox "You cancelled out!", vbOKOnly + vbExclamation, "Cancel"[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=magenta]    Exit Sub[/COLOR][/FONT]
[FONT=Fixedsys]  ElseIf Err.Number = 0 Then[/FONT][FONT=Fixedsys][COLOR=green] ' user clicked OK[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=blue]    MsgBox "Make sure VCR report is fully opened, then go back and CLICK[/COLOR][COLOR=blue] Import Lastest VCR Report.", _[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=blue]           vbOKOnly + vbInformation, "Open VCR Report"[/COLOR][/FONT]
[FONT=Fixedsys]  Else[/FONT][FONT=Fixedsys][COLOR=green] ' some other unexpected error[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=red]    MsgBox "Run-time error '" & Err.Number & "':" & vbCrLf & vbCrLf _[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=red]         & Err.Description, vbOKOnly + vbExclamation, "Error!"[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=red]    Exit Sub[/COLOR][/FONT]
[FONT=Fixedsys]  End If[/FONT]
 
[FONT=Fixedsys]  On Error GoTo 0[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

You have to put in the code for what you want to do in each of the three possible cases: when the user clicks Cancel, when the user clicks OK, and when some other unexpected error occurs.

 

[pbornemeier]

Answering Post #5 ...

I don't like using Exit Sub in the middle of code - I may leave stuff unresolved, or have to replicate any procedure closing code multiple times.

To combat this the line above my procedure closing code (Set XXX = Nothing, Application.ScreenUpdating = True, Application.StatusBar = False, etc.) in my procedures is the End_Sub: label.

If any conditions in the procedure require that the procedure be terminated, I have a Goto End_Sub: which causes execution to jump to that line and the closing code to be executed prior to exiting the module.

Sorry I left it out of my original post.