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,424
    Provided Answers: 8
    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 06:47. Reason: spelling
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •