VBA Userform (Excel 2016) - Textbox date calculations: Date + No. of days=Expiry Date

[ShyButterfly]

Thank you for checking out my query

I’m developing a form, a visitor registry, and I’ve tried to find some way of calculating the expiry date of a card.

I have searched the web for something which I can adapt to my needs, however, that hasn’t been successful. I am still struggling to understand VBA and how to know in what modules I put what code, so if you could advise me where to put the code, I’d be mighty grateful

I have uploaded a screenshot of the userform to give you the visual of what I am hoping to produce.

Sample of Visitor Registration form - expiry date.JPG


The expiry date will be dependent on which combobox dropdown option (Card Type) is selected, based on a 3 column table (name range “TBL_Card_Type (B27)” - image below), that has the value of days in the 3^rd column.

The expiry date should not fall on a weekend or holiday. (I have a separate sheet of calendar with weekends and holidays that I’ll be referring to).

*Date Issued: TXT_StartDate
*Expiry Date: TXT_CARD_ExpiryDate
Dates to be in dd/mm/yyyy format please (currently having problems with date formatting changing when added to cells in the “Visitor Card Log Sheet” – goes to US format).

Named Range TBL_Card_Type table.JPG
Please advise if there is anything you need clarification on

Kind regards,
ShyButterfly

[Bob Phillips]

Better if you post the workbook rather than screenshots.

[SamT]

Written in Excel 2003


UserForm Code:

Option Explicit

Private Function ExpiryDate(CardType As String, DateIssued As String) As String
'Use appropriate UserForm Controls as input parameters
'All UserForm Controls' Values are Strings

Dim IssueDate As Date
Dim WSFunc As WorksheetFunction
Dim DaysValid As Long
Dim tmp As Date

IssueDate = CDate(DateIssued)

'Offset since VLookUp looks in first column of table
DaysValid = WSFunc.VLookup(CardType, ThisWorkbook.Range(TBL_Card_Type).Offset(0, 1), 2, False)

tmp = DateAdd("d", DaysValid, IssueDate)

Do While IsHoliday(tmp)
  tmp = DateAdd("d", 1, tmp)
Loop
  
Do While IsWeekend(tmp)
   tmp = DateAdd("d", 1, tmp)
Loop
 
ExpiryDate = Format(tmp, "dd/mm/yyyy")

End Function

Standard Module Code.

Option Explicit

Public Function IsWeekend(DateToCheck As Date) As Boolean
   'Convert DateStrings to Dates before Calling
   'For 4day workweeks, change > 5 to > 4
   'Set First Day of Week to first workday of regular work weeks

  IsWeekend = Weekday(DateToCheck, vbMonday) > 5
End Function

Public Function IsHoliday(DateToCheck As Date) As Boolean
   'Convert DateStrings to Dates before Calling
   'You need to write this custom Function to check for local holidays

IsHoliday = False 'For Demo purposes
End Function