Solved

Stop Code after Msg Box shows

Posted on 2013-06-18
5
239 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
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 45

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 45

Expert Comment

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

Marty - MVP 2009 to 2013
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now