Solved

Stop Code after Msg Box shows

Posted on 2013-06-18
5
241 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 46

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 46

Expert Comment

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

Marty - MVP 2009 to 2013
0

Featured Post

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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