Results 1 to 3 of 3

Thread: If in VBA Excel

  1. #1
    Join Date
    Apr 2011
    Posts
    8

    Unanswered: If in VBA Excel

    Hi

    I have a macro which runs sql code. For the macro I am passing date value from Excel cell A1. The user need to enter the date manually.
    Now I need to restrict the user in entering the value in the cell A1 in the format mm/dd/yyyy .
    How include this condition in existing macro(VBA) code, so that when user enters date in someother format, a message box need to pop-up indicating to enter in the proper format?

    Welcome for all the suggestions...

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,478
    Provided Answers: 11
    isdate() will return a true/false

    so

    if not isdate(range("A1")) then Msgbox("NOT A DATE VALUE")


    Just learnt this tick write some code that will run vb on a cell change


    so in the vb part of that worksheet (not a Module) took me an 1/2 to work this out didn't read the help right LOL

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim checkdate as Boolean

    select case Target.address

    case "$A$1" ' a1 was change
    checkdate = isdate(Target.Value) if its not a date value it will be false
    if not checkdate then
    Msgbox("this is not a date value")
    else
    call sub_go_do_something(Target.Value)
    End if

    case else
    end select

    End sub

    now this can be in a module

    sub_go_do_something(thisvalue)

    msgbox("You inputed " & format(thisvalue,"mm/dd/yy") & " Date")


    End sub
    Last edited by myle; 05-17-11 at 05:47. Reason: spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Apr 2011
    Posts
    8

    VBA Excel

    Hey, thanks a lot.

Posting Permissions

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