Get date for Macro from user input

Posted on 2014-08-11
Last Modified: 2014-08-14

I have a macro consisting of 8 different sub routines that creates a report from an inserted data set. There is a lot of different formatting and sorting based on dates as compared to the current date. What I would like to do is have the macro do all the same work but based on a date input by a user. In effect, the date input by a user would then become "today's date" so any reference to "Now()" or "Date" in the macro would use the inputted date instead of the actual date.

Lastly, the date input allowed has to be restricted to a networkday and if possible holidays excluded.

It seems like capturing the user input as a variable, then referencing that variable everywhere in the macro instead of "Date" or "Now()" is the way to go so if anyone has an example of storing a user input date as a variable that would help. I'm also wondering if there is a better way.

Thanks in advance,

Question by:swjtx99

    Author Comment

    Hoping this will make more sense with this example attachment.

    The attached spreadsheet has a data set with a promise date in column I. The macro will color the cells based on current date minus the date in the cell. Example:

    If the promise date is more than 10 days past, the cell will be colored dark gray.
    If the promise date is 1 to 10 days past, Cell is colored Red
    If the promise date is equal to today, Cell is c0lored yellow
    If the promise date is 1 day in the future, Cell is colored dark green
    If the promise date is 2 days in the future, Cell is colored light green.

    The change I want to make is have the user input a date to use as a reference instead of using today. For example, If the person entered tomorrow as the reference date, then any promise dates that were tomorrow would be yellow.

    Also don't want to let the user enter a weekend date or a holiday.

    Hope this helps clarify.

    LVL 20

    Accepted Solution

    Try this, using a Inputbox to type the date.
    Default is today.
    Insert valid holidays in the Holiday list in the beginning of the code.
    I just put in 2014-08-14 and 2014-08-15 for test, use the same format as used.

    Author Closing Comment

    Hi Ejgil,

    This works perfectly! Question is, can I take the parts you inserted and make them Public so that other subroutines can reference the same "targetDate"?

    Dim InputDate As String, InputPrompt As String
    Dim Holidays() As Variant, i As Integer, j As Integer

        Holidays = Array(DateSerial(2014, 8, 14), DateSerial(2014, 8, 15))

     'Input the target date
        InputPrompt = "Input a target date"
        InputDate = Left(CStr(Now), 10)
            InputDate = InputBox(InputPrompt, "Target date", InputDate)
            If Len(InputDate) = 0 Then
                MsgBox "No value, program end"
            End If
            On Error Resume Next
            targetDate = DateValue(InputDate)
            On Error GoTo 0
            If targetDate = 0 Then
                InputPrompt = "Not a valid date, try again"
                If Weekday(targetDate, vbMonday) = 6 Then
                    InputPrompt = "Date is a Saturday, try again"
                    targetDate = 0
                ElseIf Weekday(targetDate, vbMonday) = 7 Then
                    InputPrompt = "Date is a Sunday, try again"
                    targetDate = 0
                    j = 0
                    For i = LBound(Holidays) To UBound(Holidays)
                        If targetDate = Holidays(i) Then
                            j = 1
                        End If
                    Next i
                    If j = 1 Then
                    InputPrompt = "Date is a Holiday, try again"
                    targetDate = 0
                    End If
                End If
            End If
        Loop While targetDate = 0
    LVL 20

    Expert Comment

    by:Ejgil Hedegaard
    Yes, it can be put into a separate sub, to be called from other subs.
    The variable targetDate must be available to all subs, so the Dim statement must be above the subs to be general for all subs in the module.
    Made it Public, then it can be used in other modules also, and moved the sub to input the date to another module.

    I have changed the holiday list to be on a sheet (Holidays), so it is easier to maintain than an array inside the code.
    Just add the dates in the cells below in column A.
    The range is dynamically adjusted, but there must not be any blank cells in the list.
    The sheet can be hidden.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    Digital marketing agencies have encountered both the opportunities and difficulties that emerge from working with a wide-ranging organizations.
    Viewers will learn how to apply various conditional formatting in Excel 2013.
    Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now