Hello Dave / Sam, alas the service records for WW1 were mostly destroyed by the Luftwaffe during WW2. The remaining ones that were saved from the fires are what are being used to populate the worksheets.
The forecasting of accuracy will come from known service numbers, then calculating a "likely" enrolment date from say looking at between the two dates either side that are factually known, or if there is a large gap providing a date along with a tolerance of say +/- so many days.
As more known service number / enrolment dates are entered, the theory being that the predicted dates will become more accurate. My hope is that expert's knowledge of Excel will be able to provide the most accurate method of producing a prediction.
I've been tinkering with the first part - populating the known service numbers / enrolment dates. Of course I'm struggling, but understand that the only way to learn is to try.
Option Explicit
Private Sub UserForm_Click()
Dim ShCount As Integer, i As Integer, j As Integer, ws As Worksheet
' Sort Regiment worksheets alphabetically
Application.ScreenUpdating = False
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
' Populate Regiment
For Each ws In Worksheets
cboRegiment.AddItem ws.Name
Next ws
' Populate Battalion
Dim index As Integer
index = cboRegiment.ListIndex
cboBattalion.Clear
Application.ScreenUpdating = True
End Sub
Private Sub cmbEnter_Click()
' Input known soldier's number and enlistment date
Dim n As Variant, answer As String, ws As Worksheet
Set ws = Worksheets(ActiveSheet)
n = Application.InputBox("Please Enter Soldier Number", "Enlistment Database", "Enter Number Here", , , , 1)
answer = InputBox("Please enter known enlistment date in the following format: dd-mm-yyyy", "Enlistment Database", Format(Date, "dd/mmm/yyyy"))
If answer = "" Or n = "" Then
Exit Sub
Else
If n <> "" And IsNumeric(n) = True Then
answer = Format(answer, "DD/MM/YYYY")
' Validate date
If Not IsDate(answer) Or Not answer Like "[0-2]#/[01]#/[12][08]##" Then
If MsgBox("Invalid date or invalid date format" & _
"Please enter the date in the correct format", vbRetryCancel) = vbRetry Then
Exit Sub
Else
answer = Format(answer, "DD/MMM/YYYY")
Exit Sub
End If
End If
End If
' find the end of column A
Dim x As Integer
x = 1
Do Until ws.Range("A" & x).Value = ""
x = x + 1
Loop
' last row having data = x-1
' Add data to database
ws.Range("A" & x).Value = txtSoldierNumber.Value
ws.Range("B" & x).Value = txtEnlistmentDate.Value
' Clear text boxes
txtSoldierNumber.Value = ""
txtEnlistmentDate.Value = ""
End If
End Sub
I can forsee that this will only be of use for the 1st Battalion and providing I can get it to choose the right regiment!