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
Solved

Stop Code after Msg Box shows

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

840 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