Opt out of File browser/ Macro

[Rowland Hamilton]

Folks:

If I get the file browser prompt and decide not to open a file, how can I get the macro to not perform actions on the current workbook? (Sometimes excel forces the actions on current workbook when you do not open a file as instructed by the macro). How do you stop that macro from running? Here is the code:

Sub Pull_From_Source()
'Uses file browser for source workbook, array for tab choices, find row options
'Pulls data from source workbook
Dim MasterWB As Workbook
Dim SourceWB As Workbook
Dim Incoming As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim ccDst As Range
Dim ws As Worksheet
Dim varFileName As Variant
Dim LastRow As Long
Dim FirstRow As Long
Set MasterWB = ThisWorkbook
Set Incoming = Worksheets("Incoming")
 ''''''''''Clear MasterWB'''''''''''''''''''''''''''''''''''''''''''''''''''''
    MasterWB.Activate
    Incoming.Activate
       Cells.Select
        Selection.Clear
    Range("a1").Select
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    varFileName = Application.GetOpenFilename(, , "Please select source workbook:")
    If TypeName(varFileName) = "String" Then
 
       Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0)
 
       For Each ws In SourceWB.Sheets(Array("CC10001", "35ROAR2222", "555Stomp86", "CC5353"))
            If ws.Visible <> xlSheetHidden Then
                 
                    'Expand Column groups, Collapse Row groups - for other report
                'ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
                
                'copy
                LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
                FirstRow = ws.Range("A5").End(xlDown).Row
                
              Set rngSrc = ws.Range("A" & FirstRow).CurrentRegion.Offset(0, 0) 'this works
                'Set rngSrc = ws.Range("A21").CurrentRegion.Offset(0, 0)'this works
                'Set rngSrc = ws.Range("A" & FirstRow & ":M" & LastRow) 'this works
                'Set rngSrc = ws.Range("A21:M" & LastRow)               'this works
                
                'MsgBox FirstRow 'Test if it works
 
                'paste
                Set rngDst = Incoming.Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
 
                rngSrc.SpecialCells(xlCellTypeVisible).Copy
 
                rngDst.PasteSpecial Paste:=xlPasteValues
                rngDst.PasteSpecial Paste:=xlPasteFormats
                Application.CutCopyMode = False
                
                Set ccDst = Incoming.Range("A" & Rows.Count).End(xlUp).Offset(0)
                ccDst.Formula = ws.Name
                
            End If
 
        Next ws
 
        SourceWB.Close False
 
        'MsgBox "Copied all data from source workbook"
    Else
        'MsgBox "No file selected"
    End If
    Application.Goto Incoming.Range("A1"), True
    Selection.EntireRow.Delete
End Sub

 

[Jaafar Tribak]

That's strange ! what happens if you run the line :

If varFileName = False Then

 

[phagan]

right after varFileName = ....
put

If FileName = False Then
    Exit Sub
Else

 

[Rowland Hamilton]

Awesome, Thank you, Phagan & Jaafar. - Rowland

 

[Rowland Hamilton]

Jafaar/Phagan:
Question: How can I get the command to cancel all Macros if it is in the middle of Call Macros series? I may restructure the series so it become less relevant, like Pull from all source files first then reformat all second. Even so, I still want to hit one button to perform all so it willeventually run into a problem if the Macros are separated. The reformat Macros depend on the source files being pulled properly.

[COLOR=#0070c0]Sub Run_All()[/COLOR]
[COLOR=#0070c0]  Call Pull_and_Format_Source_1[/COLOR]
[COLOR=#0070c0]  Call Pull_and_Format_Source_2[/COLOR]
[COLOR=#0070c0]End Sub[/COLOR]
[COLOR=#0070c0][/COLOR]
[COLOR=#0070c0]Sub Pull_and_Format_Source_1()[/COLOR]
[COLOR=#0070c0]  Call Pull_Source_1[/COLOR]
[COLOR=#0070c0]  Call Format_Source_1[/COLOR]
[COLOR=#0070c0]End Sub[/COLOR]
[COLOR=#0070c0][/COLOR]
[COLOR=#0070c0]Sub Pull_and_Format_Source_2()[/COLOR]
[COLOR=#0070c0]  Call Pull_Source_2  [/COLOR]
[COLOR=#0070c0]    Call Format_Source_2[/COLOR]
[COLOR=#0070c0]End Sub[/COLOR]

Also note:There are 2 "varFileName = ", but luckily I got it figured out:

  varFileName = Application.GetOpenFilename(, , "Please select source workbook, (Hint: Diamond Division Report):")
    If varFileName = False Then
    MsgBox ("No file Selected")
    Exit Sub
    
    Else
   Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0)

Thank you - Rowland

 

[Norie]

Are Pull_Source1 and Pull_Source2 competely different?

 

[Rowland Hamilton]

Norie:
Yes, the "Pull from Source" Macros are separate from the "Reformat Data" macros. Data comes from separate soures/divisions so it is separated. I can group them in different ways and probably will group Pull and Source separately:

Sub Run_All()
  Call Pull_Source_1 'Diamond Division
  Call Pull_Source_2 'Ruby Division
  'Stop
  Call Reformat_1  'Diamond Division
  Call Reformat_2 'Ruby Division
End Sub

Thank you, Rowland

 

[phagan]

I would probably use an OnError switch.
at the begining of the code

On Error goto MyError

At the end of the code:

MyError:
msgbox "There was an Error", vbcritical, "Error!"
on error goto 0 
exit sub

That will allow it to exit the sub and stop processing right where the error happened. But the draw back to that is that it will still get through the process to that point. Try doing a search for custom error handling.