Get date for Macro from user input


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,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

swjtx99Author Commented:
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.

Ejgil HedegaardCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swjtx99Author Commented:
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
Ejgil HedegaardCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.