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