Calling 2 macros sequentially when using Private Sub Worksheet_Change(ByVal Target As Range)

[gravity1000]

Hallo

I am having a problem with calling 2 macros sequentially when using Private Sub Worksheet_Change(ByVal Target As Range) .

What I want to happen looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$2" Then
Call Macro1
Call Macro2
End If
End Sub

However. when I change the value in $O$2 only Macro2 runs; Macro1 does not run. When I run Macro1 by itself, it does run fine. The problem is that I want Macro1 to run FIRST on a change in $O$2 because it supplies changed data for use in the output of Macro2.

What am I doing wrong????

Anyone able to help me?

 

[mikerickson]

Its hard to tell without seeing Macro1 and Macro2, but if either of them chages the value of O2, you may need to set Application.EnableEvents to False and later reset it to True.

 

[gravity1000]

Wow, thanks for such a fast response Mike! No, the value of O2 is changed by another external program. When that happens I want Macro1 to run first, and THEN for Macro2 to run.

This is the one I want to run first:

Sub Macro1()
Dim X As Long ' Dim all variables
Dim MyVar As Variant
Dim MyString As String
On Error Resume Next ' ignore errors
Application.DisplayAlerts = False ' turn off DDE warning messages
X = DDEInitiate("Ext_prog", "Topic") ' connect to Ext_prog
MyVar = DDERequest(X, "Field") ' get Field
MyString = MyVar(1) ' convert to a string
Cells(2, 9).Value = MyString ' put value in O2
End Sub

and then I want this one to run:

Sub Macro2()
'
' Set file details
Set filePath = [File_details!C2]

' Set document name
Set docName = [File_details!C3]

Open filePath For Output As #1

'Write header to file
outputText = [File_details!C5] & docName & [File_details!C6]
Print #1, outputText

'Start to loop through stations
For Each cell In [actions!I2]

pmXXXXX = cell.Offset(0, 0)
YYYYValue = cell.Offset(0, 1)
ZZZZZValue = cell.Offset(0, 2)
pmAAAAA = cell.Offset(0, 3)
pmBBBBB = cell.Offset(0, 5)
pmBBBBB = cell.Offset(0, 6)


If pmXXXXX = "" Then
Exit For
End If

'Create a placemark
outputText = [File_details!C8] & pmXXXXX & " " & pmBBBBB & " " & pmAAAAA & [File_details!C9] & YYYYYValue & ", " & ZZZZZValue & [File_details!C10] & [File_details!C11]
Print #1, outputText

Next

'Write footer to file
outputText = [File_details!C13]
Print #1, outputText

Close #1
'
End Sub

O2 is the pmXXXXX value in Macro2.

Thanks!

 

[gravity1000]

Problem solved. Please close this thread.