Solved

Stop Code after Msg Box shows

Posted on 2013-06-18
5
246 Views
Last Modified: 2013-06-18
I call this sub in another procedure - If the dates do not match, I need for the process to stop.  Instead it continues the code in the other procedure after the msg box shows.  Is there a way to stop the code from running after the error message - I have tried Exit Sub also.

Sub CompareDates()
    Dim oFS As Object
    Dim strFilename As String
    Dim r As Range
    Dim dat1 As Date, dat2 As Date
    'Put your filename here
    strFilename = "P:\abc.txt"

    Set oFS = CreateObject("Scripting.FileSystemObject")

Set r = Sheet1.Range("c3")
dat1 = CDate(r.Value)
dat2 = CDate(oFS.GetFile(strFilename).DateCreated)

If Format(dat1, "mm/dd/yyyy") <> Format(dat2, "mm/dd/yyyy") Then
    MsgBox ("Not current file!")
Exit Sub
End If

End Sub
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 39257025
Change the Sub to a Function which returns boolean. Instead of Exit Sub, use Return False and place a Return True at the end. Then you call it like this:
If CompareDates() Then
  'true code
Else
  'stop code
End If
0
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 145 total points
ID: 39257041
Create a Public variable like

Public gbFatalError As Boolean

Then in the code you posted do this

gbFatalError = False
If Format(dat1, "mm/dd/yyyy") <> Format(dat2, "mm/dd/yyyy") Then
    MsgBox ("Not current file!")
    gbFatalError = True
Exit Sub

Open in new window


And finally in the procedure the follows do this

If gbFatalError Then Exit Sub ' or Function
0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 145 total points
ID: 39257047
Adding to Cluskitt,

Do this

Function CompareDates() as boolean
    Dim oFS As Object
    Dim strFilename As String
    Dim r As Range
    Dim dat1 As Date, dat2 As Date
    'Put your filename here
    strFilename = "P:\abc.txt"
    CompareDates = false 
    Set oFS = CreateObject("Scripting.FileSystemObject")

Set r = Sheet1.Range("c3")
dat1 = CDate(r.Value)
dat2 = CDate(oFS.GetFile(strFilename).DateCreated)

If Format(dat1, "mm/dd/yyyy") <> Format(dat2, "mm/dd/yyyy") Then
    MsgBox ("Not current file!")
 CompareDates = true
Exit Sub
End If

End Function 

Open in new window


Then on your other sub

you can do this (using Cluskitt's code)

If CompareDates() Then
  'true code - dates are not equal
Else
  'stop code
End If 

Open in new window

0
 

Author Closing Comment

by:leezac
ID: 39257518
I used both comments so I awarded equal.  I did not use together of course but in different subs.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39257539
I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question