two Excel VBA interfere with each other

[xueming]

Hi,

I have two small excel VBA tools, working perfectly individually. But If I run them at the same time (Both are active), then it cause all sorts of problem. If I minimize one of the tool, then it works fine also.

Is there anything I can do to make both Excel VBA work at the same time ?

Thanks in advance!!

 

[Richard Schollar]

Hi

Please post both sets of code as it is impossible to help without it

 

[xueming]

Thanks. But I only have one set of code.

When I click this button, it cause problem

Private Sub GenerateReport_Click()
Dim Answer As String
Dim MyNote As String
Dim MyCSR As String
Dim MyFile As String
    MyCSR = GetCSRNumber
    MyFile = "C:\FRC\timelines\" & MyCSR & ".txt"
    
    WriteRangeToTextFile Range("A4:C11"), MyFile, ": "
    Shell "notepad.exe " & MyFile, vbNormalFocus
    
    
End Sub

Sub WriteRangeToTextFile(Source As Range, Path As String, Delimiter As String)
    Dim oFSO As Object
    Dim oFSTS As Object
    Dim lngRow As Long, lngCol As Long
    Dim MyCell As Date
    

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFSTS = oFSO.CreateTextFile(Path, True)
     
    oFSTS.Write "**********CIC Emergency Timeline**********" & vbCrLf & vbCrLf
     
    For lngRow = 1 To Source.Rows.Count
        For lngCol = 1 To Source.Columns.Count
            If lngCol = 2 Then
            If Source.Cells(lngRow, lngCol) = "" Then
              oFSTS.Write "N/A"
            Else
            
            MyCell = Source.Cells(lngRow, lngCol)
                    oFSTS.Write MyCell
             End If
            ElseIf lngCol = Source.Columns.Count Then
            
                'Check to see if the cell is empty
                If Source.Cells(lngRow, lngCol) = "" Then
                    oFSTS.Write vbCrLf
                Else
                   
                   
                   oFSTS.Write " : " & Source.Cells(lngRow, lngCol) & vbCrLf
                End If
                
            Else
                oFSTS.Write Source.Cells(lngRow, lngCol) & Delimiter
            End If
        Next lngCol
    Next lngRow
    oFSTS.Close
    Set oFSTS = Nothing
    Set oFSO = Nothing
End Sub

 

[Richard Schollar]

So you have a button on a worksheet, you click said button and that calls the writeRangeToTextFile code and writes the text on that sheet to a text file. So what goes wrong?

 

[xueming]

After I click the button, my two seperated excel windows start to flash and won't stop. looks like it went into some kind of loop between two excel windows. And I have to use taskmanager to kill them.

But if I minimize the other one, then it works fine.

 

[Richard Schollar]

What happens if you step thru the code eg click the cursor in the GenerateReport_Click sub and press F8 repeatedly. Does the code fail/get stuck at any particular line?

 

[xueming]

Just tried and it just keep doing what it has been doing and not stop at anywhere

 

[Richard Schollar]

I've sent you a PM - please read it

 

[xueming]

Thank you so much and I have sent you an email.