Interrupting Procedure Disables all other Procedures/Events

[music_al]

I have a piece of code which just has the user select a file that is imported into the current workbook as a new worksheet.

If the user doesnt actually select a file and click OK but instead clicks cancel, this then causes all other code not to run. Seems to affect Worksheet events like Worksheet SelectionChange and Worksheet_Change. This is the code...

Sub GetFilePath()
    Dim TrackerName As String
    Set myFile = Application.FileDialog(msoFileDialogOpen)
    With myFile
        .Title = "Choose the RRAR to be imported"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        RRAR_File = .SelectedItems(1)
    End With
    
    Application.Workbooks.Open (RRAR_File)
    
    RRAR_Filename = Dir(RRAR_File)
    
    TrackerName = ThisWorkbook.Name
    Windows(TrackerName).Activate
    
    'Windows.Arrange ArrangeStyle:=xlHorizontal
    
    Windows(RRAR_Filename).Activate
    Sheets(1).Name = "RRAR_Form"
    'Copy the RRAR Form to the RRAR Tracker
    Sheets("RRAR_Form").Copy After:=Workbooks(TrackerName).Sheets(1)
    'Sheets("RRAR_Form").Copy After:=Workbooks("RRAR_And_Candidate_Tracker DEVELOPMENT 05.xlsm").Sheets(1)
    
    Application.DisplayAlerts = False
    Windows(RRAR_Filename).Close
    
    Application.DisplayAlerts = True
    
    ActiveWindow.WindowState = xlMaximized


End Sub

Any help would be appreciated. Thanks in advance.

Al

 

[silentwolf]

Hi,
following code I use and it works fine for me maybe you can addapt it to your needs..

    If Not booFileWasChosen Then
        MsgBox "No File has been selected"
        Exit Sub
    End If

Sub VerzeichnisOeffnen()
    Dim fd As FileDialog
    Dim booFileWasChosen As Boolean
    
    Dim QWB As Workbook
    Dim ZWB As Workbook
    Dim QWS As Worksheet
    Dim ZWS As Worksheet
    
    Dim strDatei As String             
    Dim strName As String

    
    Set fd = Application.FileDialog(msoFileDialogOpen)
    
    fd.Filters.Clear
    fd.Filters.Add "Alte Excel Dateien", "*.xls"
    fd.Filters.Add "Neue Excel Dateien", "*.xlsx"
    fd.Filters.Add "Macro Excel Dateien", "*.xlsm"
    fd.Filters.Add "Alle Excel Dateien", "*.xl*"
    fd.Filters.Add "Alle CSV Dateien", "*.csv"
    
    fd.FilterIndex = 4
    
    fd.AllowMultiSelect = False
    
    fd.InitialFileName = Verzeichnis
        
    fd.Title = "File Open"
    fd.ButtonName = "Go!"
    
    booFileWasChosen = fd.Show
    
    If Not booFileWasChosen Then
        MsgBox "Keine Datei wurde ausgewählt"
        Exit Sub
    End If
    
    Datei = fd.SelectedItems(1)
    
    Workbooks.Open (Datei)
    
    strName = ActiveWorkbook.Name
    
'    MsgBox "Der Dateipfad heisst: " & Datei & vbCrLf _
'            & strName
        
    If strName Like "*.csv" Then
        fd.Execute              'Ausgewählte Datei Öffnen
    Else
    
        Set ZWB = ThisWorkbook
        Set QWS = Worksheets(1)     'Source
        Set ZWS = tblMyFile           ' Destination "I use the code name of the worksheet so that is why this is set to tblMyFile
        
        QWS.Cells.Copy ZWS.Cells(1, 1)      ' Insert to Destination Worksheet at Range("A1")
    
        Workbooks(strName).Close
    End If
    
    Unload Me
End Sub

Public Property Let Verzeichnis(strVerzeichnis As String)
    m_strVerzeichnis = strVerzeichnis
End Property
Public Property Get Verzeichnis() As String
    Verzeichnis = m_strVerzeichnis
End Property

I fortot to post that too.. I use Properties to Set the "Directory"

 

[music_al]

I should add the procedure before it as that might help...

Sub Import_RRAR()
    'This module allows a RRAR Form to be imported
    GetFilePath
    Copy_RRAR_Data
    On Error GoTo ExitSub
    Application.DisplayAlerts = False
    Sheets("RRAR_Form").Delete
    Application.DisplayAlerts = True
    
    Sheets("RRAR_Log").Select
ExitSub:
    Application.DisplayAlerts = True

End Sub


Sub GetFilePath()
    Dim TrackerName As String
    Set myFile = Application.FileDialog(msoFileDialogOpen)
    With myFile
        .Title = "Choose the RRAR to be imported"
        .AllowMultiSelect = False
        If .Show <> -1 Then
        Application.EnableEvents = True
        
[B][COLOR=#ff0000]            Exit Sub[/COLOR][/B]
        End If
        RRAR_File = .SelectedItems(1)
    End With
    
    Application.Workbooks.Open (RRAR_File)
    
    RRAR_Filename = Dir(RRAR_File)
    
    TrackerName = ThisWorkbook.Name
    Windows(TrackerName).Activate
        
    Windows(RRAR_Filename).Activate
    Sheets(1).Name = "RRAR_Form"
    'Copy the RRAR Form to the RRAR Tracker
    Sheets("RRAR_Form").Copy After:=Workbooks(TrackerName).Sheets(1)
    
    Application.DisplayAlerts = False
    Windows(RRAR_Filename).Close
    
    Application.DisplayAlerts = True
    
    ActiveWindow.WindowState = xlMaximized


End Sub



Sub Copy_RRAR_Data()
    Dim FormRole As String
    'Set_RRAR_Log_Variables
    
    On Error GoTo ExitSub
    
    Application.EnableEvents = False
    
    With Worksheets("RRAR_Log")
        FormRole = Worksheets("RRAR_Form").Range("H7")
        .Cells(RRAR_Log_RowsUsed + 1, RRAR_Log_Role_Col) = FormRole
        RRAR_Log_RowsUsed = .UsedRange.Rows.Count
    
    End With
    Application.EnableEvents = True
    
ExitSub:
    Exit Sub


End Sub

I'm already exiting the procedure if the user clicks Cancel.

 

[Fluff]

Try

[COLOR=#0000ff]Dim Flg As Boolean[/COLOR]
Sub Import_RRAR()
    'This module allows a RRAR Form to be imported
    GetFilePath
    If Flg Then Exit Sub
    Copy_RRAR_Data
    On Error GoTo ExitSub
    Application.DisplayAlerts = False
    Sheets("RRAR_Form").Delete
    Application.DisplayAlerts = True
    
    Sheets("RRAR_Log").Select
ExitSub:
    Application.DisplayAlerts = True

End Sub

The line in blue must go at the very beginning of the module, before any code.
And add this line to GetFilePath

 If .Show <> -1 Then
        Application.EnableEvents = True
        [COLOR=#ff0000] Flg = True[/COLOR]
            Exit Sub
        End If