Date Problem in User Form Search

[Denny57]

I use the following code to ensure that a time is correctly formatted in a TextBox.

Private Sub TextBox1_Afterupdate()
Dim tString As String
With TextBox1
'Check if user put in a colon or not
If InStr(1, .Value, ":", vbTextCompare) = 0 Then
'If not, make string 4 digits and insert colon
tString = Format(.Value, "0000")
tString = Left(tString, 2) & ":" & Right(tString, 2)

TextBox1.Value = Format(TimeValue(tString), "HH:MM")
Else
'Otherwise, take value as given
.Value = Format(.Value, "hh:mm")
End If
End With
End Sub

However, when I try to recall a record to the User Form using the time as the search value I get the message that the search value cannot be found. To test the search functionality I changed the code to search for a different record. The search actually functions as expected with the exception that the time value returned is 00:01.

I have set the cell format for this record to custom format "hh:mm".

Example
Keyed Value in User Form = 1234
AfterUpdate Value = 12:34
Value displayed in Cell = 12:34
Value displayed in Formula Bar = 12:34:00
Value returned in User Form = 00.01 (when search is performed using a different search criteria)

I am assuming that the reason why the search by "time" does not work is because the actual cell value and the displayed cell value are different.

Unfortunately, the "time" is the only unique value and is the only suitable record by which to make a search. In addition, this means that if the record is subsequently updated, then this value will be incorrect.

As ever, any help is greatly appreciated.

 

[6StringJazzer]

The textbox contains a string "12:34", not a time, so it will not match a time value. When you do the search, where you are currently using TextBox1 use TimeValue(TextBox1)

 

[Denny57]

The textbox contains a string "12:34", not a time, so it will not match a time value. When you do the search, where you are currently using TextBox1 use TimeValue(TextBox1)

Below is the start of the code I use to search for a unique record in the worksheet.

I use the txtRaceTime text box to input the search value. The AfterUpdate code converts both the input and search value to hh:mm format and the correcponding field in the worksheet is custom formatted to hh:mm and the formula bar shows a time value. Thius as far as logic determines, the format of the value in txtRaceTime is hh:mm and not text.

I have tried changing the txtRaceTime to TimeValue(txtRaceTime) to no avail. in fact the error Debug shows a problem at

txtRaceTime.Value = Format(TimeValue(tString), "HH:MM")

in the AfterUpdate code, even if I change txtRaceTime to TimeValue(txtRaceTime)

Search Code
Private Sub cmdViewRecord_Click()
'Used to search for a unique Race Time in the database and return all corresponding values to the user form
'This Search function should be used ONLY when updating or viewing full records

Worksheets("Selections").Activate

Dim Res As Variant
Dim lastrow
Dim myFind As String

Res = Application.Match(txtRaceTime, Sheets("Selections").Range("C2:C70"), 0)

If IsError(Res) Then
MsgBox "Race Time Not Found", vbInformation, "Race Time Not Found"
Call UserForm_Initialize
txtRaceTime.SetFocus
Exit Sub
End If

lastrow = Sheets("Selections").Range("C" & Rows.Count).End(xlUp).Row
myFind = txtRaceTime
For Currentrow = 2 To lastrow
If Cells(Currentrow, 2).Text = myFind Then
txtBetCode.Value = ActiveSheet.Cells(Currentrow, 1).Value

I would appreciate the adjusted code to allow this serch to work

 

[Denny57]

I have realised that the "Time" element is crucial to other parts of my current project so I think it better if I start a new post setting out my aims. Hopefully ther is a solid solution which I ca n use

 

[6StringJazzer]

You have confused a couple of things.

Thius as far as logic determines, the format of the value in txtRaceTime is hh:mm and not text.

The content of any text box is always text, full stop. It doesn't matter how you format it. It is text.

I have tried changing the txtRaceTime to TimeValue(txtRaceTime) to no avail. in fact the error Debug shows a problem at

txtRaceTime.Value = Format(TimeValue(tString), "HH:MM")

That is not what I described. After you do all of your formatting, you apply TimeValue at the point where you do the search.

Make the changes in red below.

(I strongly suggest marking code with code tags to ensure correct spacing)

Worksheets("Selections").Activate

Dim Res As Variant
Dim lastrow
Dim myFind As Double

    Res = Application.Match(txtRaceTime, Sheets("Selections").Range("C2:C70"), 0)
 
    If IsError(Res) Then
        MsgBox "Race Time Not Found", vbInformation, "Race Time Not Found"
    Call UserForm_Initialize
    txtRaceTime.SetFocus
    Exit Sub
    End If
 
lastrow = Sheets("Selections").Range("C" & Rows.Count).End(xlUp).Row
myFind = TimeValue(txtRaceTime)
For Currentrow = 2 To lastrow
   If Cells(Currentrow, 2).Text = myFind Then
      txtBetCode.Value = ActiveSheet.Cells(Currentrow, 1).Value

 

[Denny57]

Sorry if I am being ignorant of VBA and Excel, however, I am beginning to think that time formats and VBA are not the best of bedfellows.

I use this code to convert input values to "hh:mm" format and understand that this will be as text in Excel

Private Sub txtRaceTime_Afterupdate()
'Converts the keyed information into "hh:mm" format

Dim tString As String
With txtRaceTime
    'Check if user put in a colon or not
    If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        'If not, make string 4 digits and insert colon
        tString = Format(.Value, "0000")
        tString = Left(tString, 2) & ":" & Right(tString, 2)
       
        [B]txtRaceTime.Value = Format(TimeValue(tString), "hh:mm")[/B]
    Else
        'Otherwise, take value as given
        .Value = Format(.Value, "hh:mm")
    End If
End With
End Sub

I have applied the changes you have highlighted in red for the search function

Records write correctly to the worksheet

When I insert a valid value into the time textbox and start the search, the error message box appears "Race Time Not Found" and the code stops. When I check the debug the highlighted field is where the code stops working.

I have tried using different code to convert the structure and have changed the ws column format to Text.
Now I get a different error but at least the search is finding the value in the ws.

Private Sub txtRaceTime_AfterUpdate()
If Len(txtRaceTime.Text) = 4 And InStr(txtRaceTime.Text, " ") = 0 Then
    txtRaceTime.Text = Left(txtRaceTime, 2) & ":" & Right(txtRaceTime, 2)
End If
End Sub

I may have to resort to using an unformatted 4 digit value as trying to get a time format to function seems unnecessarily complicated. Unless I can get some value to be imported back into the user form I will have to accept defeat

 

[Denny57]

Sorry the conversion to VBA removed the highlighting.

The code stops at txtRaceTime.Value = Format(TimeValue(tString), "hh:mm")

 

[6StringJazzer]

When I insert a valid value into the time textbox and start the search, the error message box appears "Race Time Not Found" and the code stops. When I check the debug the highlighted field is where the code stops working.

I missed this line of code, which needs the same change.

    Res = Application.Match(TimeValue(txtRaceTime), Sheets("Selections").Range("C2:C70"), 0)

Sorry if I am being ignorant of VBA and Excel, however, I am beginning to think that time formats and VBA are not the best of bedfellows.

It's actually quite simple if you recognize the difference between a time value and text. Anything in a textbox is always text. 1234 is text. 5/20/2022 is text. 5:00 PM is text. To compare any of these to a number/date/time you have to convert it first.

The code stops at txtRaceTime.Value = Format(TimeValue(tString), "hh:mm")

and your code has this

tString = Left(tString, 2) & ":" & Right(tString, 2)

txtRaceTime.Value = Format(TimeValue(tString), "hh:mm")

First you are bulding tString as a text representation of a time. Then you are converting it to a time with the TimeValue function. Then you are using Format to convert it back to text. There is no need for all the back & forth. You can just do this, and there should be no error:

        txtRaceTime.Value = Left(tString, 2) & ":" & Right(tString, 2)

(Note that if you want to apply formatting to code in your post, using plain VBA tags will not allow it. You have to use the RICH code tags to be able to add formatting.)

 

[Denny57]

Thank you for all your help. I have messed aroung with my coding with several different suggestions that I think I need to go back to the original (which I backed-up and start again.

Having made your suggested changes, once again a search cannot now find a match, although I do not get code errors. This will be because the process stops if a match cannot be found. Incidentally I have dropped the tstring conversion to the shorter version

Private Sub txtRaceTime_AfterUpdate()
If Len(txtRaceTime.Text) = 4 And InStr(txtRaceTime.Text, " ") = 0 Then
    txtRaceTime.Text = Left(txtRaceTime, 2) & ":" & Right(txtRaceTime, 2)
End If
End Sub

I cannot believe that VBA is so complicated when all I am trying to do is send and receive a detail in ??:?? / hh:mm format from and to a User Form TextBox

 

[6StringJazzer]

It would help if you also showed the current state of the code that does the search.

The way that professional programmers typically do this is to validate the input as close to the point of entry as possible. They would use a control in the user interface that requires the user to enter a valid time in the first place, and returns a time value to the code. There are such controls available for VBA UserForms.

It's not VBA itself that is complicated, but you are taking a complicated approach. The reason you are having trouble is that you are allowing your user to enter any text string, and then try to turn it into a time. BTW your method will also allow the the user to enter things like "2574" and convert it to "25:74".