Stop a VBA Code error

[gheyman]

This code uses GetOpenFilename and opens up a file window so that the user can select a Workbook

The problem I am having is when a user changes their mind and doesn't select a workbook. If this happens the user gets a Run-time error which I never want to happen.

How do I stop this from happening?

Sub GetWBS()

Application.ScreenUpdating = False
    
    Dim BOE_WrkBook As Workbook
    Dim Pricing_WrkBk As Workbook
    Dim File_Used As String
    Dim PricingLastRow As Long
    Set Pricing_WrkBk = ThisWorkbook
    
            FileUsed = Application.GetOpenFilename _
                (Title:="GoTo Consolidation File for WBS Dictionary", _
                    FileFilter:="Excel Files *.xls* (*.xls*),")
            
            ThisWorkbook.Sheets("WBS_Dictionary").Range("I6") = FileUsed
            Workbooks.Open FileUsed
            
    Set BOE_WrkBook = ActiveWorkbook

             If Not Evaluate("isref(WBS_Dictionary!a1)") Then
               MsgBox "The Selected Workbook does not contain a WBS_Dictionary tab, check to validate the tab is named correctly or select a different Workbook"
               Exit Sub
            End If
            
                'Clear
                Pricing_WrkBk.Sheets("WBS_Dictionary").Activate
                Range("C2:C6").Select
                Selection.ClearContents
                Range("F2:G6").Select
                Selection.ClearContents
                Range("I5").Select
                Selection.ClearContents
                Range("B9:Q5000").Select
                Selection.ClearContents
                Range("I6").Select
                Selection.ClearContents
                
                'Eneter which file was used and when
                ThisWorkbook.Sheets("WBS_Dictionary").Range("I5") = FileUsed
                ThisWorkbook.Sheets("WBS_Dictionary").Range("I6").Value = Now
                
            
                    BOE_WrkBook.Sheets("WBS_Dictionary").Activate
               BOELstRow = Sheets("WBS_Dictionary").Range("A" & Rows.Count).End(xlUp).Row

                    ActiveWorkbook.Sheets("WBS_Dictionary").Range("C2:C6").Activate
                Selection.Copy
               
                    Pricing_WrkBk.Sheets("WBS_Dictionary").Activate
                    Range("C2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False
'
                   BOE_WrkBook.Sheets("WBS_Dictionary").Activate
                   Range("F2:F6").Select
               Selection.Copy
'
                    Pricing_WrkBk.Sheets("WBS_Dictionary").Activate
                    Range("F2:F6").Select
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False
'
                    BOE_WrkBook.Sheets("WBS_Dictionary").Activate
                    Range("A9:I" & BOELstRow).Select
                Selection.Copy
'
                    Pricing_WrkBk.Sheets("WBS_Dictionary").Activate
                    Range("A9").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False
'
                   BOE_WrkBook.Sheets("WBS_Dictionary").Activate
                   Range("O9:P" & BOELstRow).Select
                Selection.Copy
'
                    Pricing_WrkBk.Sheets("WBS_Dictionary").Activate
                    Range("J9").Select
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False
                   
                Application.CutCopyMode = False
                
                Range("C2").Select
         
                BOE_WrkBook.Close False
                
                
                    Pricing_WrkBk.Sheets("WBS_Dictionary").Activate
                PricingLastRow = Sheets("WBS_Dictionary").Range("C" & Rows.Count).End(xlUp).Row
                
                Range("L9:L" & PricingLastRow).FormulaR1C1 = "=IF((ISERROR(INDIRECT(RC3&""!$L$3"")=FALSE))=FALSE,TRUE,FALSE)"
                Range("M9:M" & PricingLastRow).FormulaR1C1 = ActiveCell.FormulaR1C1 = "=IF(RC10=""Child"",R2C14,"""")"
                Range("N9:N" & PricingLastRow).FormulaR1C1 = "=IF(RC10=""Child"",R3C14,"""")"
                Range("O9:O" & PricingLastRow).FormulaR1C1 = "=IF(RC10=""Child"",R4C14,"""")"
                Range("P9:P" & PricingLastRow).FormulaR1C1 = "=IF(RC10=""Child"",R5C14,"""")"
                
                
Application.ScreenUpdating = True
         
End Sub

 

[Fluff]

Try

If FileUsed = False Then Exit Sub

 

[jproffer]

If you want to just cancel the whole routine, you could do something like this:

By the way, you Dim File_Used, but you're using FileUsed

FileUsed = Application.GetOpenFilename _
                (Title:="GoTo Consolidation File for WBS Dictionary", _
                    FileFilter:="Excel Files *.xls* (*.xls*),")

If FileUsed = "" Then Exit Sub

 

[cerfani]

FileUsed = Application.GetOpenFilename(Title:="GoTo Consolidation File for WBS Dictionary", FileFilter:="Excel Files *.xls* (*.xls*),")

If [COLOR=#0000ff](test if not valid filename)[/COLOR] Then
    Exit Sub
End If

ThisWorkbook.Sheets("WBS_Dictionary").Range("I6") = FileUsed
Workbooks.Open FileUsed

you need to make sure it is a valid filename before using

read this...

https://www.thespreadsheetguru.com/...tion-to-determine-if-excel-file-name-is-valid

 

[gheyman]

Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback