[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

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,

  • 2
  • 2
1 Solution
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.
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.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now