Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel 2003 DateCheck procedure

    Hi,

    I have an Excel 2003 workbook that contains about a dozen different VBA procedures in 5 modules. What I would like to do is lock the procedures up from functioning on a specified date, and display a message indicating updating is required.

    Instead of inserting the code in each separate procedure, I would like to write just one procedure in a module of its own (DateCheck), and call it at the beginning of each of the others as needed. The first procedure below, DateCheck, appeared to work when included as a whole up to the “Else” in the other procedures, but as written as a separate procedure, I am lost as to how to make it exit and continue the procedure that called it if false. As an example, the second procedure, SortAscending, is one of the procedures that I would be inserting the call to DateCheck.

    Any thoughts on this would be appreciated.


    Sub DateCheck()

    Dim Currdate
    Dim Newdate

    currdate = Date
    newdate = #1/1/2014#

    If currdate >= newdate Then

    MsgBox ("Happy New Year! Program Update Required.")

    Else

    Exit Sub

    End If

    End Sub

    ************************************************** ************

    Sub SortAscending()

    DateCheck

    ActiveSheet.Unprotect

    [b3].CurrentRegion.Sort _
    Key1:=[b3], Order1:=xlAscending, _
    Key2:=[c3], Order2:=xlAscending, _
    Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    ActiveSheet.Protect
    ActiveWorkbook.Save

    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    You need to code the DateCheck as a Function not a SUB then it becomes very easy to exit out of any Sub calling it if the check fails.

    Code:
    Function DateCheck() as Boolean
    
    Dim Currdate
    Dim Newdate
    
    currdate = Date
    newdate = #1/1/2014#
    
    If currdate >= newdate Then
    
    MsgBox ("Happy New Year! Program Update Required.")
     DateCheck = True
    
    Else
       DateCheck = False
    End If
    
    End Functioin
    
    ************************************************** ************
    
    Sub SortAscending()
    
      If DateCheck() Then Exit Sub
    
    ActiveSheet.Unprotect
    
    [b3].CurrentRegion.Sort _
    Key1:=[b3], Order1:=xlAscending, _
    Key2:=[c3], Order2:=xlAscending, _
    Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    ActiveSheet.Protect
    ActiveWorkbook.Save
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,492
    Thanks
    28
    Thanked 171 Times in 167 Posts
    Hi RG

    ..or you can leave it as a subroutine, but just cease all further vba processing after the message is displayed:

    Code:
    Sub DateCheck()
    
    Dim Newdate
    
    Newdate = #1/1/2014#
    
    If Date >= newdate Then
    
    MsgBox ("Happy New Year! Program Update Required.")
    
    End		'discontinue all further processing
    
    End if
    
    End Sub
    zeddy

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you both for your replies. Both of these work when included in each of the modules that call DateCheck, but not when placed in a module by itself and called from the other modules. I am guessing that I am not calling it correctly from the other modules. Having DateCheck in its own module would allow for only having to change the date in one place, instead of in each of the other modules if it has to be included in each.

    Zeddy's version stops the routines if returned True, even after clicking the OK button on the message, which is exactly what I need it to do. What would the proper call be that would go in each of the other modules? I have tried DateCheck and DateCheck(), or is an If statement needed as in RG's?

    Thank you once again.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    You just need to declare the Function or Sub as Public, e.g. Public Function DateCheck() as Boolean
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Me again... Tried your suggestion in several different ways, including both your function and Zeddy's Sub. Still works if they are in the same module as one of the existing routines, but not outside of it, and only for the routine whose module it is in.

    Maybe pics of what I am doing will help show what I am messing up. Here are one each of a Clear Module using the Function and the Sub, and one each of the DateCheck function and sub.

    Hopefully they make some sort of sense. Thank you

    Func.JPG
    DateCheckFunc.JPG
    ClearSub.JPG
    DateCheckSub.JPG

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    In the Function version you changed the name of the function but didn't change it in the body of the function where True or False is assigned!. These assignments must match the name of the function w/o the (). HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •