two Excel VBA interfere with each other

xueming

New Member
Joined
Nov 25, 2009
Messages
6
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!!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

xueming

New Member
Joined
Nov 25, 2009
Messages
6
Thanks. But I only have one set of code.

When I click this button, it cause problem

Code:
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

Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Nov 25, 2009
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Nov 25, 2009
Messages
6

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top