Help with DateADD function

I have been working on a database to collate a range of activities within a prison setting, that measures those activities against Equalities and Diversity protected characteristics, i.e race gender etc etc

I have attached a sample sheet, with regards to the one issue I am currently having, because of the nature of data protection laws, I have populated this with some fictitious names and details, This is an Macro Enabled .xlsm file in Excel 2013.

On the attached sample are four sheets

"Home" ... starting point, contains links to the two userforms

"Lists" ... contains a selection of Named Ranges, for use in the comboboxes on the two user forms

"Prisoners" ... stores the basic data that populates the other userforms (accessed by clicking the "Residents Protected Characteristics" button on the home sheet)

"SCRB" .... this stores data about an event ..... (accessed by clicking the add "Add / update SCRB" button on the home sheet"

It is the userform F_SCRB that I am having issues with.

You load a prisoners details into the userform by clicking into the combobox "CbFind" located at the top of the userform next to the text label that reads "Add SCRB". There are number of textboxes, that are hidden from view that store the prisoners details, that then populate the spreadsheet, when you add or save the record, all that is visible is their nomis number (Prisoner Number) forename and surname ... don't worry about those details for now

I have a text box "TxDate" that records the date that an SCRB event occurs, this is formatted as below

Private Sub TxDate_AfterUpdate()  ' changes date box into dd-mmm-yy value
Dim dDate As Date
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TxDate.Value = Format(TxDate.Value, "dd mmm yy")
On Error GoTo 0
End Sub

Likewise their is a review date "TxReview" that is similarly formatted, so I won't repost the code.

The issue I have is a combobox that is supposed to populate "TxReview" the coding for that is

Private Sub CbDuration_Change()

If CbDuration.Value = "28 Days" Then
TxReview.Value = DateAdd("d", 28, TxDate.Value)
TxReview_AfterUpdate
End If

If CbDuration.Value = "2 Months" Then
TxReview.Value = DateAdd("m", 2, TxDate.Value)
TxReview_AfterUpdate
End If

If CbDuration.Value = "3 Months" Then
TxReview.Value = DateAdd("m", 3, TxDate.Value)
TxReview_AfterUpdate
End If

End Sub

If you look at the example that I have loaded onto thea userform accessed by clicking into combobox "CbFind" .... next to the textlabel that reads "Update" ...

TxDate value is 2nd Feb

CbDuration value is "3 months"

TX Review value is 5th Feb .... but should be 2nd May

I hope the above make sense, please feel free to play around with the userforms attached and let me know if there is any reason the dataAdd function in "Private sub CbDuration_Change" is not working correctly

Thanks in advance

Iain C

Thank you Gisjmo

This seems to be a lot more stable, I will play around with adding records etc .... if it works all the time, I will report my issue as fixed.

Iain C

Thank you this does appear to have resolved this "bug"

Iain C