Posts by IainC

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

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

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

PDF files would be okay, providing whatever the end format is can be pasted into a written monthly report

Iain C

Example Sheet.xlsx

I have been working on a database to collate Equalities & Diversity data for an organisation, the issue I currently have is how to save a series of ranges or chart objects, as an image file, each month, so that we are able to collate historical reports and not just current ones.

So I have two related questions, I have searched through the forums and haven’t really found an answer that works for me.

I have attached a sample with two sheets

Sheet 1 “Data_Age” collates the age range data live from a separate sheet that is updated from a userform.

• So I want to be able to save range (“A3:B12”) as a jpeg or png image
• I want to save it to a specific directory on my laptop namely “C:\Users\Iain\Desktop\Database\Images”
• The Month … Range(“D1”) and Year … Range (“G1”) are fed from another sheet, and the Data title is in Range (“A1”) … Data by Age

…. but I want to be able to save the file in the following format “YY-MM-Data by Age.(image either jpeg or png) … So something like “21-Nov-Data by Age.jpeg”

Sheet 2 “Chart_Age” is a visual representation of the data, so as above

• I want to save the Chart Object as an image
• In the same directory as above and in a similar format but something like “21-Nov-Data by Age Chart.jpeg”

I hope my ramblings above make sense

Iain C.

Because the database I am working on is hopefully going to capture a range of Equalities and Diversity data for a Prison, I have attached a sanitised version to show what I am intending

Hope you can assist

Iain C

I am putting together a userform and am having issues with some text box's, I would like to solve the following issue. I am using Excel 2013

I have trawled through the forum and can't seem to find an answer, so I will apologise if this has been asked before

On the userform I have created three text boxes

"TxDoB " text box with Date of Birth value

In "TxAge" - I would like this to show age as an integer, displayed as a whole number, just as you would for your age, so roounded down rather than rounded up

In "TxRange" I would like to display an age range i.e "18-21" or "22-29" or "30-39" etc

Entering the date of birth is no issue as below

Private Sub TxDoB_AfterUpdate()
Dim dDate As Date
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TxDoB.Value = Format(TxDoB.Value, "d mmm yy")
On Error GoTo 0
TxAge_Change
' updates Age and populate Age & Age Range textbox's
End Sub

I have a cell on a worksheet Sheet6.Range("D1") which displays today's date ie NOW() formatted as "D MMM YY"

So from this I am populating "TxAge" using the formula below

Private Sub TxAge_Change()
Dim As Integer
On Error Resume Next
TxAge.Value = ((Sheet6.Range("D1").Value) - (TxDoB.Value) / 365.25)
On Error GoTo 0
TxRange_Change
End Sub

First question is how do I get the value in "TxAge" to display as a whole number, rounded down.

Second question is I want the value in "TxAge" to populate "TxRange", I can do it in a long winded way

i.e

Private Sub TxRange_Change()
On Error Resume Next
If TxAge.Value < 22 Then TxRange.Value = "18-21"
End If
ElseIf TxAge.Value = 22 Then TxRange.Value = "22-29"
End If
ElseIf TxAge.Value = 23 Then TxRange.Value = "22-29"
End If
ElseIf TxAge.Value = 24 Then TxRange.Value = "22-29"
End If


'etc
'etc


On Error GoTo 0
End Sub

Can I simplify this to populate "TxRange" with the following values

"18-21"

"22-29"

"30-39"

"40-49"

"50-59"

etc

Thanks in advance if this sounds like a silly question

IainC