Hi, I am running this macro as a filter, "Filter_Report_15", in a series of
macros but the messge box will not display. The purpose of the display is to
alert the user to update the database with the correct value.

The code is below:
Cells.Select

If Worksheets("Report 15").Range("Z2") = "Y" And Worksheets("Report
15").Range("M2") = "N" Then Reponse = MsgBox("The Subject to Arrow Assessment
checkbox has not been ticked. Please amend the Assessment in IRM", vbOKOnly)
If Response = 0 Then Call Finish

The "call Finish" takes the program to the last macro in the series
correctly and executes ok but the "Exit Sub" command only takes us back to
the next line in the "Filter_Report_15" macro. I wish this macro to end the
series.

I have tried the code below but it does not stop the series.


Windows("Report Creation.xls").Activate
Sheets("Report Creation").Select
With ActiveWorkbook
.RunAutoMacros xlAutoClose
'.Close
End With

End Sub

Any help greatly appreciated.

Davie

Just had a Homer Simpson moment. I am referencing the wrong row, row 2 and
not the filtered row. I will look at changing this.
I still need to know how to end the series macro.

Davie
"davethewelder" wrote:

Hi, I am running this macro as a filter, "Filter_Report_15", in a series of
macros but the messge box will not display. The purpose of the display is to
alert the user to update the database with the correct value.

The code is below:
Cells.Select

If Worksheets("Report 15").Range("Z2") = "Y" And Worksheets("Report
15").Range("M2") = "N" Then Reponse = MsgBox("The Subject to Arrow Assessment
checkbox has not been ticked. Please amend the Assessment in IRM", vbOKOnly)
If Response = 0 Then Call Finish

The "call Finish" takes the program to the last macro in the series
correctly and executes ok but the "Exit Sub" command only takes us back to
the next line in the "Filter_Report_15" macro. I wish this macro to end the
series.

I have tried the code below but it does not stop the series.


Windows("Report Creation.xls").Activate
Sheets("Report Creation").Select
With ActiveWorkbook
.RunAutoMacros xlAutoClose
'.Close
End With

End Sub

Any help greatly appreciated.

Davie

If Response = 0 Then
Call Finish
Exit Sub
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"davethewelder"
wrote in message
Just had a Homer Simpson moment. I am referencing the wrong row, row 2 and
not the filtered row. I will look at changing this.
I still need to know how to end the series macro.

Jim, thanks for that, it does stop the macro.

Cheers,

Davie

"Jim Cone" wrote:


If Response = 0 Then
Call Finish
Exit Sub
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"davethewelder"
wrote in message
Just had a Homer Simpson moment. I am referencing the wrong row, row 2 and
not the filtered row. I will look at changing this.
I still need to know how to end the series macro.

Jim, when I run it in the full series it goes to the Finish macro, which is
the last in the series, and then goes to the next macro down from
Report_15_Filter. This continues to run the rest of the series with
gobledegook produced.

Davie

"davethewelder" wrote:

Jim, thanks for that, it does stop the macro.

Cheers,

Davie

"Jim Cone" wrote:


If Response = 0 Then
Call Finish
Exit Sub
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"davethewelder"
wrote in message
Just had a Homer Simpson moment. I am referencing the wrong row, row 2 and
not the filtered row. I will look at changing this.
I still need to know how to end the series macro.

If your Report_15_Filter sub is called by another sub then
code will continue to run until that other sub runs out of calls.
For instance in the sequence below, when each macro is done the next
sub in the call sequence is run.

Sub OriginalMacro
Call Macro2
Call Report_15_Filter
Call Macro3
CallMacro4
End Sub

The order in which Subs are placed in a module has no effect
on when (or if) a sub is run.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"davethewelder"
wrote in message
Jim, when I run it in the full series it goes to the Finish macro, which is
the last in the series, and then goes to the next macro down from
Report_15_Filter. This continues to run the rest of the series with
gobledegook produced.
Davie



"davethewelder" wrote:
Jim, thanks for that, it does stop the macro.
Cheers,
Davie



"Jim Cone" wrote:
If Response = 0 Then
Call Finish
Exit Sub
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"davethewelder"
wrote in message
Just had a Homer Simpson moment. I am referencing the wrong row, row 2 and
not the filtered row. I will look at changing this.
I still need to know how to end the series macro.

Jim, I have copied all the code into one module and it works fine. Thanks
for your help.

Davie

"Jim Cone" wrote:

If your Report_15_Filter sub is called by another sub then
code will continue to run until that other sub runs out of calls.
For instance in the sequence below, when each macro is done the next
sub in the call sequence is run.

Sub OriginalMacro
Call Macro2
Call Report_15_Filter
Call Macro3
CallMacro4
End Sub

The order in which Subs are placed in a module has no effect
on when (or if) a sub is run.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"davethewelder"
wrote in message
Jim, when I run it in the full series it goes to the Finish macro, which is
the last in the series, and then goes to the next macro down from
Report_15_Filter. This continues to run the rest of the series with
gobledegook produced.
Davie



"davethewelder" wrote:
Jim, thanks for that, it does stop the macro.
Cheers,
Davie



"Jim Cone" wrote:
If Response = 0 Then
Call Finish
Exit Sub
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"davethewelder"
wrote in message
Just had a Homer Simpson moment. I am referencing the wrong row, row 2 and
not the filtered row. I will look at changing this.
I still need to know how to end the series macro.