Posts by IainC

    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

    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

    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.

    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
    ' 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
    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


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







    Thanks in advance if this sounds like a silly question