#1
|
|||
|
|||
Verify date based on dropdown selection
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!! |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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 |