Verify date based on dropdown selection

kkline116 · #1 12-11-2023, 06:18 AM

Hello,

I'm looking for a vba code where, if "Reclassifications", "Transfers", and "Change of pay rate" are selected in a dropdown, it goes back to the date field and verifies the date selected is a Monday (any Monday).

Reason being, that's the start of the pay period, and we're getting people choosing the date they're filling out the form, not a Monday. That Monday could be a week or two back, or a week or two in the future, so I just need it verify it's a Monday date.

If it's not a Monday, I'd like it to display a message that says, "Effective date must be a Monday (the first day of a pay period). Please enter the correct date."

Bonus points if we're able to then select the Effective date field / box so that when they hit "OK" on the error, the Calendar popout is displayed so they just pick the date.

I have attached a quick form for your use if it would be helpful.

Many thanks in advance for any help you can provide!!

Guessed · #2 12-11-2023, 07:58 PM

Set the title in the date CC to 'Effective Date' and then add this code to the ThisDocument module

Code:

Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
  Dim ccDate As ContentControl, dteEffectiveDate As Date
  If oCC.Title = "Type of Transaction" Then
    Select Case oCC.Range.Text
      Case "Reclassification", "Transfer", "Change of Pay Rate"
        Set ccDate = ActiveDocument.SelectContentControlsByTitle("Effective Date")(1)
        If Not ccDate.ShowingPlaceholderText Then
          dteEffectiveDate = CDate(ccDate.Range.Text)
          If Format(dteEffectiveDate, "ddd") <> "Mon" Then
            ccDate.Range.Select
            MsgBox "You silly billy, the effective date has to be a Monday now you've chosen that!", vbOKOnly + vbCritical, "WTF"
          End If
        End If
    End Select
  End If
End Sub

kkline116 · #3 12-27-2023, 11:57 AM

Quote:

Originally Posted by Guessed

Set the title in the date CC to 'Effective Date'

First, my apologies for the late response, it's been a hectic couple of weeks...

2nd, THANK YOU for your help with this...

The Code looks great... but maybe I don't know how to set the title? I went to the Effective Date CC, right clicked and went to Table Properties, then went to "Alt Text" and put in Effective Date in the "Title" field:

I get an error that says, "Run-time error '5941': The requested member of the collection does not exist." When I hit Debug, it highlights the line:

Code:

Set ccDate = ActiveDocument.SelectContentControlsByTitle("Effective Date")(1)

Thanks in advance for any help!!

kkline116 · #4 12-27-2023, 12:00 PM

Oops... I got it... just double click in Design Mode, first option..

Thanks for the help Andrew!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Text based on selection of dropdown list	cloud67	Word VBA	2	08-09-2019 06:46 AM
Reveal portion of document based on dropdown selection	chappeja	Word VBA	1	03-27-2019 08:36 PM
Selection of a dropdown creates another dropdown wih the list	krishnamurthy.ka2810	Word VBA	1	04-26-2018 11:44 PM
Dropdown selection value	coconutt	Word VBA	5	09-13-2012 05:23 PM
Macro to populate a text form field based on dropdown selection	koloa	Word	0	10-20-2011 11:52 AM