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.

 

[Denny57]

Thank you for your help with this..
By resetting the original code , using brief code to format the date information and using .Text instead of .Value when recalling the details everything now works as I had hoped.