Results 1 to 6 of 6

A little VBA with date inputs and msgbox checks

This is a discussion on A little VBA with date inputs and msgbox checks within the Excel Questions forums, part of the Question Forums category; Hi, I'm trying to force a user to input a date as "mm/yyyy" format only. I'm using a msgbox to ...

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    59

    Default A little VBA with date inputs and msgbox checks

    Hi,

    I'm trying to force a user to input a date as "mm/yyyy" format only. I'm using a msgbox to capture the user response and if it passes, I go onto my next step. Otherwise, I want to have the user correct the date input format. I have the following code that I believe is almost there, but something isn't working quite right since it gets stuck in an endless loop. Here is the code:

    Code:
    Sub start_date()
    Dim start_date As Integer
    Dim UserEntry As String
    Dim Msg As String
    Dim TheDate As String
    Msg = "Enter Date as mm/yyyy"
    TheDate = Format(Date, "mm/yyyy")
        
        Do
            UserEntry = InputBox(Msg)
            If UserEntry = "" Then Exit Sub
            If UserEntry = TheDate Then
                ActiveSheet.Range("B16").Value = UserEntry
                Exit Sub
            Else
                Msg = "Please try again.  Enter date as mm/yyyy"
            End If
        Loop
    End Sub
    Any help is greatly appreciated. Thanks in advance!!

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,565

    Default Re: A little VBA with date inputs and msgbox checks

    Why not just validate the date then format the date as you wish - see VBA - Ask for date & check if it falls on a Monday

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    59

    Default Re: A little VBA with date inputs and msgbox checks

    Hi Vog II,

    Thanks for the link and your response. Would this method work if I absolutely need the date to be on the first of every month?

    The reason I need this is that my program cycles many times, aggregates the different iterations and then plots them based on the month that they fall. This happens over and over again. If the days are not on the first of the month, my "index" and "match" functions won't work very well.

    Thanks!

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    59

    Default Re: A little VBA with date inputs and msgbox checks

    Hi Vog II,

    I see what you meant. I think I have what I need now. Here's the final code:


    Code:
    Sub start_date()
    Dim start_date As Integer
    Dim UserEntry As String
    Dim Msg As String
    Dim TheDate As String
    Msg = "Enter Date as mm/yyyy"
        
        Do
            UserEntry = InputBox(Msg)
            If UserEntry = "" Then Exit Sub
            If IsDate(UserEntry) Then
                ActiveSheet.Range("B16").Value = Format(UserEntry, "mm/yyyy")
                Exit Sub
            Else
                Msg = "Please try again.  Enter date as mm/yyyy"
            End If
        Loop
    End Sub
    Thanks again for you help and the link!!

  5. #5
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,565

    Default Re: A little VBA with date inputs and msgbox checks

    In formula

    =DATE(YEAR(A1),MONTH(A1),1)

    which should be translatable into VBA.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    59

    Default Re: A little VBA with date inputs and msgbox checks

    That new formula you gave me works great as well to force the input to be on the first of the month! That's something new for me so thanks again.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com