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

 

[Ruddles]

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.

Normally I would agree - don't Exit Sub unless you're sure you've taken care of anything which needs taking care of.

In this case, as there is nothing to take care of, I thought it safe. In any case, it's not normally difficult, even if some code has to be repeated.

Personally speaking I wouldn't use GoTo to jump around the program as I believe that leads beginners into poor program design and 'spaghetti code'. Rather than:-

[FONT=Fixedsys]  sFilePathName = Application.GetOpenFilename("All Files (*.doc*), *.doc*")[/FONT]
[FONT=Fixedsys]
  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"[/FONT]
[FONT=Fixedsys]
End_Sub:[/FONT]
[FONT=Fixedsys]End Sub[/FONT]

I would do this:-

[FONT=Fixedsys]  sFilePathName = Application.GetOpenFilename("All Files (*.doc*), *.doc*")[/FONT]
[FONT=Fixedsys]
  If sFilePathName = "" Then
      MsgBox "No file selected.  Cannot continue."[/FONT]
[FONT=Fixedsys]  Else
      MsgBox "Make sure VCR report is fully opened, then go back and CLICK Import Lastest VCR Report.", vbInformation, "Open VCR Report"[/FONT]
[FONT=Fixedsys]  End If
[/FONT]
[FONT=Fixedsys]End Sub[/FONT]

I firmly believe the VBA editor should refuse to accept a GoTo except when used in an On Error directive.

 

[sdoppke]

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.

This works great!! really appreciate all the help from both of you

sd

 

[Ruddles]

Thanks for the feedback.