Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 54

Thread: Predict Date of Enlistment

  1. #21
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    HTSCF allowing users to make an informed guestimate of their own, as U outlined, seems like it would provide the end user with the most benefit. You can trial this to fix your date format concerns. HTH. Dave
    If Right(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), 4) <= 1900 Then
    frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
    Else
    frmEnlistment.txtEnlistmentDate.Text = CStr(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1))
    End If

  2. #22
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Nice one, thanks Dave!

    I'm really surprised that Microsoft hasn't fixed what I am sure is a very common issue for VBA programmers.

  3. #23
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Okay, I've thought out how I want to search / display the results of the query, but my VBA knowledge (and G**gle) has let me down.

    Private Sub cmbEnquiry_Click()
    
        Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
        Dim Sht As Worksheet, SortRange As Range
    
        ' Enlistment date query
        ' If number already exists, then show it and the date, plus 5 either side in txtEstEnlistmentDateResult
        ' If number doesn't exist, then show 5 nearest numbers and their date either side in txtEstEnlistmentDateResult
    
        If frmEnlistment.ListBox1.ListIndex = -1 Then
            MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If frmEnlistment.ListBox2.ListIndex = -1 Then
            MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If frmEnlistment.txtSoldierNumberEnq = vbNullString Then
            MsgBox "Input Soldier Number to Query!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        
        For Each Sht In ThisWorkbook.Sheets
            If frmEnlistment.ListBox1.List(frmEnlistment.ListBox1.ListIndex) = Sht.Name Then
                With Sheets(Sht.Name)
                    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    For Cnt = 1 To LastCol
                        If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.ListBox2.List(frmEnlistment.ListBox2.ListIndex) Then
                            LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
                            For Cnt2 = 3 To LastRow
                                
                                
                                
                                
                                
                                Exit For
                            End If
                        Next Cnt
                        Exit For
                    End With
                End If
            Next Sht
            
            frmEnlistment.txtSoldierNumberEnq.Text = vbNullString
            frmEnlistment.txtEstEnlistmentDateResult.Text = vbNullString
            frmEnlistment.ListBox1.ListIndex = -1
            frmEnlistment.ListBox2.ListIndex = -1
    End Sub
    Attached Files Attached Files

  4. #24
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Still plodding my way through.

    The enlistment date enquiry function needs to enable the user to select Regiment and Battalion and a Soldier Number to enquire about.

    If the Soldier number exists, then the result of the Soldier Number and Enlistment Date need to be displayed in txtEstEnlistmentDateResult with a short message indicating that this is the case.

    If the Soldier Number doesn't exist, then the txtEstEnlistmentDateResult box should show five enlistment numbers and their respective enlistment dates either side of the entered Soldier Number. Again, ideally with a message to explain that an exact Soldier Number / Enlistment Date combination was not found.

    Private Sub cmbEnquiry_Click()
    
        Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
        Dim Sht As Worksheet, SortRange As Range, Solnumberdate As Variant
    
        ' Enlistment date query
        ' If number already exists, then show it and the date, plus 5 either side in txtEstEnlistmentDateResult
        ' If number doesn't exist, then show 5 nearest numbers and their date either side in txtEstEnlistmentDateResult
    
        If frmEnlistment.lboRegiment.ListIndex = -1 Then
            MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If frmEnlistment.lboBattalion.ListIndex = -1 Then
            MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If Not IsNumeric(frmEnlistment.txtSoldierNumberEnq.Value) Or _
           frmEnlistment.txtSoldierNumberEnq.Text = vbNullString Then
            MsgBox "Input Soldier Number to Query!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        
        For Each Sht In ThisWorkbook.Sheets
            If frmEnlistment.lboRegiment.List(frmEnlistment.lboRegiment.ListIndex) = Sht.Name Then
                With Sheets(Sht.Name)
                    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    For Cnt = 1 To LastCol
                        If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.lboBattalion.List(frmEnlistment.lboBattalion.ListIndex) Then
                            LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
                            For Cnt2 = 3 To LastRow
                                If CInt(Sheets(Sht.Name).Cells(Cnt2, Cnt)) = CInt(frmEnlistment.txtSoldierNumberEnq.Value) Then
                                    frmEnlistment.txtEnlistmentDate.Text = vbNullString
                                    ' Display existing Soldier Number and Enlistment Date in txtEstEnlistmentDateResult
                                
                                    frmEnlistment.txtSoldierNumberEnq.txtEstEnlistmentDateResult
                                    
                                    
                                    Exit Sub
                                End If
                            Next Cnt2
                            
                            Exit For
                        End If
                    Next Cnt
                    Exit For
                End With
            End If
        Next Sht
        
        frmEnlistment.txtSoldierNumberEnq.Text = vbNullString
        frmEnlistment.txtEstEnlistmentDateResult.Text = vbNullString
        frmEnlistment.lboRegiment.ListIndex = -1
        frmEnlistment.lboBattalion.ListIndex = -1
    End Sub
    I was wondering if the Offset or Resize method would help with selecting a range of five Soldier Number / Enlistment Dates?

    Really struggling to sort this so help would be very much appreciated! I keep looking and trying things without any sucess. Leave it for a while and research / enter a few more known Soldier Numbers / Enlistment Dates, then go back to it again.
    Attached Files Attached Files

  5. #25
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    HTSCF I don't understand why U want the user to select Regiment and Battalion and then enter a Soldier Number to enquire about? Is the regiment and battalion always known? Why not just enter the soldier number and then report the regiment and battalion if known? I've been unable to resolve the 1900 date problem... when the date is retrieved, even though entered as for example 04-01-1900 (d-m-y), the 1900 dates are 1 day off when returned ie. 03-01-1900 is returned re. the leap year. This code is better than last, but still needs to be adjusted to accommodate the leap year.
    If Left(CDate(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1)), 4) <> 1900 ThenfrmEnlistment.txtEstEnlistmentDateResult.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
    Else
    frmEnlistment.txtEstEnlistmentDateResult.Text = CStr(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1))
    End If
    Dave

  6. #26
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Dave, thanks for your reply.

    I see why you're asking about whether the Regiment and Battalion are known, but most people who are researching someone will "usually" know what Regiment someone was in and more often or not the Battalion too. The idea was to provide an idea of when someone might've enlisted, which is usually because so many records were destroyed in the Blitz, meaing that this detail was missing. Regiments and Battalions can be found on other types of war records, but not Enlistment Dates.

    I was thinking that it would become too complicated if just a soldier number were entered, which might potentially give a number of permutations across Regiments / Battalions.

    Incidentally, not all soldier numbers were made up purely of numerals, some were prefixed with "G/", "G", "L/", "M", "M2", "T/", "T2/", "T3/", "SE/" or "SS/" to describe but a few. I haven't tried entering any of these yet as the Hampshire Regiment uses standard numbers. But looking at the code I see that the Soldier Number input is checking for a numeral.

    I'm not understanding how the date issue fits in when trying to read and retrieve from existing data. Mind you it wouldn't be the first time I've missed something obvious. The date entry seems to be working fine at the moment and is allowing entry of dates prior to and after 1900 without issue so far. Or are you trying to allow for someone who might've enlisted on February 29th? The form will only be used to find an Enlistment Date from the Soldier Number and not the other way around.

    I've altered the form and code to use camelback to try and adhere to best VBA principles as per #24.

    Thanks again!

  7. #27
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    "Incidentally, not all soldier numbers were made up purely of numerals, some were prefixed with "G/", "G", "L/", "M", "M2", "T/", "T2/", "T3/", "SE/" or "SS/" to describe but a few".... argh!!! I'm at a loss as to how you would standardize any process to predict enrollment dates if the soldiers aren't simply sequentially numbered? As for the date, entry doesn't seem to be the problem. As stated, "when the date is retrieved, even though entered as for example 04-01-1900 (d-m-y), the 1900 dates are 1 day off when returned ie. 03-01-1900 is returned" The indented code is great except it doesn't usually post well. Here's the code for your "Enquiry" button... it doesn't do the 5 before and after thing but it is a start. I think the prefix thing will make the whole thing moot anyways. Dave
    Private Sub cmbEnquiry_Click()
    
    Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
    Dim Sht As Worksheet, SortRange As Range, Flag As Boolean
    
    
    ' Enlistment date query
    ' If number already exists, then show it and the date, plus 5 either side in txtEstEnlistmentDateResult
    ' If number doesn't exist, then show 5 nearest numbers and their date either side in txtEstEnlistmentDateResult
    
    
    If frmEnlistment.ListBox1.ListIndex = -1 Then
    MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
    Exit Sub
    End If
    If frmEnlistment.ListBox2.ListIndex = -1 Then
    MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
    Exit Sub
    End If
    If frmEnlistment.txtSoldierNumberEnq.Text = vbNullString Then
    MsgBox "Input Soldier Number to Query!", vbExclamation + vbOKOnly, "Soldier Enlistment"
    Exit Sub
    End If
    
    
    For Each Sht In ThisWorkbook.Sheets
    If frmEnlistment.ListBox1.List(frmEnlistment.ListBox1.ListIndex) = Sht.Name Then
    With Sheets(Sht.Name)
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For Cnt = 1 To LastCol
    If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.ListBox2.List(frmEnlistment.ListBox2.ListIndex) Then
    LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
    For Cnt2 = 3 To LastRow
    If CInt(Sheets(Sht.Name).Cells(Cnt2, Cnt)) = CInt(frmEnlistment.txtSoldierNumberEnq.Value) Then
    If Left(CDate(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1)), 4) <> 1900 Then
    frmEnlistment.txtEstEnlistmentDateResult.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
    Else
    frmEnlistment.txtEstEnlistmentDateResult.Text = CStr(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1))
    End If
    Flag = True
    Exit For
    End If
    Next Cnt2
    If Not Flag Then
    frmEnlistment.txtEstEnlistmentDateResult.Text = "Soldier not Enlisted!"
    End If
    Exit For
    End If
    Next Cnt
    End With
    End If
    Next Sht
    End Sub

  8. #28
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    This is great Dave, thanks!

    I didn't mean to try and throw a curved ball in and should've explained just a little better. Some Regiments did use letter prefixes, but this was standard for the whole battalion, so anyone searching one of those would already know to put the prefix in front of any soldier number that was located. So the number system is just fine.
    Attached Files Attached Files
    Last edited by HTSCF Fareha; 01-18-2021 at 12:51 PM.

  9. #29
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    I think I've read somewhere that using 'select' is a bad thing, but for trying to select five Soldier Numbers / Enlistment dates either side of a Soldier Number query, I was wondering if something along the lines of this might work? I've picked C10 out of randomness.

    Range(Range("C10").Offset(-5, 0), Range("C10").Offset(5, 0)).Select

  10. #30
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    I'm getting a date problem when the user inputs 03/01/1900 (d/m/y), as the entry goes in as 01/03/1900. If you "cheat" and put the date as 01/03/1900 to get the desired result of 03/01/1900, it goes in okay.

    Private Sub cmbEnter_Click()
        ' Input known soldier's number and enlistment date to add to selected Regiment / Battalion
        Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
        Dim Sht    As Worksheet, SortRange As Range
    
        If frmEnlistment.lboRegiment.ListIndex = -1 Then
            MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If frmEnlistment.lboBattalion.ListIndex = -1 Then
            MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If Not IsNumeric(frmEnlistment.txtSoldierNumber.Value) Or _
           frmEnlistment.txtSoldierNumber.Text = vbNullString Then
            MsgBox "Enter Soldier Number!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If Not IsDate(frmEnlistment.txtEnlistmentDate.Text) Or _
           frmEnlistment.txtEnlistmentDate.Text = vbNullString Then
            MsgBox "Enter date in Day-Month-Year format!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
    End If
        
    For Each Sht In ThisWorkbook.Sheets
    If frmEnlistment.lboRegiment.List(frmEnlistment.lboRegiment.ListIndex) = Sht.Name Then
    With Sheets(Sht.Name)
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For Cnt = 1 To LastCol
    If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.lboBattalion.List(frmEnlistment.lboBattalion.ListIndex) Then
    LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
    For Cnt2 = 3 To LastRow
    If CInt(Sheets(Sht.Name).Cells(Cnt2, Cnt)) = CInt(frmEnlistment.txtSoldierNumber.Value) Then
    frmEnlistment.txtEnlistmentDate.Text = vbNullString
    MsgBox "Soldier number already exists!", vbExclamation + vbOKOnly, "Soldier Enlistment"
    frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
    Exit Sub
    End If
    Next Cnt2
    .Cells(LastRow + 1, Cnt) = frmEnlistment.txtSoldierNumber.Value
    .Cells(LastRow + 1, Cnt + 1) = Format(frmEnlistment.txtEnlistmentDate.Value, "dd/mm/yyyy")
                            
    ' Sort out 1900 date issue
    If Left(CDate(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1)), 4) <> 1900 Then
    frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
    Else
    frmEnlistment.txtEnlistmentDate.Text = CStr(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1))
    End If
    Exit For
    End If
    Next Cnt
    Exit For
    End With
    End If
    Next Sht
        
    With Sheets(Sht.Name)
    Set SortRange = .Range(.Cells(3, Cnt), .Cells(LastRow + 1, Cnt + 1))
    End With
    With SortRange
    .Sort Key1:=Cells(3, Cnt), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
        
    frmEnlistment.txtSoldierNumber.Text = vbNullString
    frmEnlistment.txtEnlistmentDate.Text = vbNullString
    frmEnlistment.lboRegiment.ListIndex = -1
    frmEnlistment.lboBattalion.ListIndex = -1
    End Sub
    Or is this because of the leap year issue? Entering 23/01/1900 (d/m/y) goes in correctly.
    Then 03/02/1900 (d/m/y) inputs as 02/03/1900, with 13/02/1900 (d/m/y) correctly again.

    It appears that every date in 1900 that has a day that could also be a month i.e. from the 1st through to the 12th, then these are entered back to front. Any date after 12th is entered correctly.
    Last edited by HTSCF Fareha; 01-24-2021 at 12:06 AM.

  11. #31
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    HTSCF I see your still at this. I don't have any idea why the date thing can't be resolved... I tried and failed.
    Here's 4 soldiers either side of an unknown.... 5 soldiers made for poor presentation. I'm guessing once you add the prefixes, quite a bit of code change will be required. Dave
    Attached Files Attached Files

  12. #32
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Many thanks, Dave. Not a post that I was expecting, but extremely well received!

    The date thing is really strange. I'm going to keep searching to see if there is an explanation / solution.

    The soldier number bit works great!! I really appreciate you taking the time to stick with it and coming up with a solution.

    I've attached the file along with a few "cosmetic" changes and further enlistment date updates!

    Steve
    Attached Files Attached Files
    Last edited by HTSCF Fareha; 01-26-2021 at 04:37 AM.

  13. #33
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    You are welcome Steve. Here's some more cosmetic changes and a functional change re. clearing listboxes when using the Enquiry. Hope U will like them. Dave
    Attached Files Attached Files

  14. #34
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Woah, the colours certainly pop! Possibly a bit too much for my eyes.

    I'll take a closer look at the code tomorrow. Thanks!

  15. #35
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    I like the clearing of the enlistment date predictions - much neater!

    I was wondering if there would be a way of adding another 'If Else' statement to the date format function, so that any dates after 1900, that also have a day of 1 to 12, might switch the dd and mm about?

    Private Sub cmbEnter_Click()
        ' Input known soldier's number and enlistment date to add to selected Regiment / Battalion
        Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
        Dim Sht As Worksheet, SortRange As Range
    
        If frmEnlistment.lboRegiment.ListIndex = -1 Then
            MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If frmEnlistment.lboBattalion.ListIndex = -1 Then
            MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If Not IsNumeric(frmEnlistment.txtSoldierNumber.Value) Or _
           frmEnlistment.txtSoldierNumber.Text = vbNullString Then
            MsgBox "Enter Soldier Number!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        If Not IsDate(frmEnlistment.txtEnlistmentDate.Text) Or _
           frmEnlistment.txtEnlistmentDate.Text = vbNullString Then
            MsgBox "Enter date in Day-Month-Year format!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            Exit Sub
        End If
        
        For Each Sht In ThisWorkbook.Sheets
            If frmEnlistment.lboRegiment.List(frmEnlistment.lboRegiment.ListIndex) = Sht.Name Then
            With Sheets(Sht.Name)
            LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            For Cnt = 1 To LastCol
            If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.lboBattalion.List(frmEnlistment.lboBattalion.ListIndex) Then
            LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
            For Cnt2 = 3 To LastRow
            If CInt(Sheets(Sht.Name).Cells(Cnt2, Cnt)) = CInt(frmEnlistment.txtSoldierNumber.Value) Then
            frmEnlistment.txtEnlistmentDate.Text = vbNullString
            MsgBox "Soldier number already exists!", vbExclamation + vbOKOnly, "Soldier Enlistment"
            frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
            Exit Sub
            End If
            Next Cnt2
            .Cells(LastRow + 1, Cnt) = frmEnlistment.txtSoldierNumber.Value
            .Cells(LastRow + 1, Cnt + 1) = Format(frmEnlistment.txtEnlistmentDate.Value, "dd/mm/yyyy")
                            
            ' Sort out 1900 date issue
            If Left(CDate(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1)), 4) <> 1900 Then
            frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
            Else
            frmEnlistment.txtEnlistmentDate.Text = CStr(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1))
            End If
            Exit For
            End If
            Next Cnt
            Exit For
            End With
            End If
        Next Sht
        
        With Sheets(Sht.Name)
            Set SortRange = .Range(.Cells(3, Cnt), .Cells(LastRow + 1, Cnt + 1))
        End With
        With SortRange
            .Sort Key1:=Cells(3, Cnt), Order1:=xlAscending, _
                  Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End With
        
        frmEnlistment.txtSoldierNumber.Text = vbNullString
        frmEnlistment.txtEnlistmentDate.Text = vbNullString
        frmEnlistment.lboRegiment.ListIndex = -1
        frmEnlistment.lboBattalion.ListIndex = -1
    End Sub

  16. #36
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    Steve, again, I don't have any idea why the date thing can't be resolved... I tried and failed. You have a UK OS and I have a Canadian OS so I don't seem to be able to enter dates like your data ie. with slashes. Not that it matters re. the 1900 date problem. Here's am interesting read and possible solution. Perhaps starting a new thread about the whole issue would be warranted. Dave
    How to Work with Dates Before 1900 in Excel - ExcelUser.com

  17. #37
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Snap! I saw that too when trying to find a solution. I've also read that the leap year issue is so that users changing from Lotus 123 to Microsoft Excel can migrate their data. Down to the fact that the "error" was with Lotus who were trying to make their product fit in a smaller memory size. Annoyingly, this doesn't help the majority of us Excel users!

    I'll do a dig around this forum before raising the issue. Definitely a good idea!

    One last thing before I close this thread off - is there a small piece of code that could fit into that shown in post #34 (after the final 'End With'?) that could do a count of all the records using the 'Regiment' and 'Battalion' combination, providing the result in a textbox called 'txtTotalRecords' after the 'cmbEnter' button is pressed?

    Thanks again Dave!

    Steve

  18. #38
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    835
    Location
    Remove the location and 1900 problem by replacing the date entry and retrieval with strings. Dave
    Attached Files Attached Files

  19. #39
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,724
    Location
    Quote Originally Posted by Dave View Post
    Remove the location and 1900 problem by replacing the date entry and retrieval with strings. Dave

    Agree - not sure why this is a separate thread?????

    http://www.vbaexpress.com/forum/show...ly-Interpreted

    I've only been looking at the Date part

    However, the interpolation (if that's what's going on) to guess an enlistment date based on the input and two surrounding soldier numbers would have to be done using the VBA dates
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #40
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,724
    Location
    Thought occurs

    Instead of trying to do 3 functions on one user form, IMHO it'd be easier to maintain and more user friendly to have 3 user forms, each only doing a single function

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •