Formatting to display time to millisecond value in combobox and linked cell output

Question

I am working on a worksheet that allows the user to filter data between two time values of pre-recorded data. The time format on this data goes down to the tenth of a second (HH:MM:SS.0) and while the combobox list will display all of these values, when one is selected, it is truncated to just the second value. I've done a bit of looking and found a lot of info on calling time to the millisecond for the CURRENT time, but not for just formatting a display. Here is how my combobox code currently reads:

Private Sub ComboBox21_Change()

With ComboBox21
    .Value = Format(.Value, "hh:mm:ss")
End With

End Sub

I've tried a few different options including adding ".0" at the end, but no luck. Also, I've noticed that the value in the linked cell when a selection is made seems to mirror what the selection itself is. I was under the impression that the linked cell would show the relative number of the selection to the total number of available selections and this is what I actually need. Does anyone know how to fix this?

Looks like a good answer is [here - Display Milliseconds In Excel](http://stackoverflow.com/a/3095530/4717755) — PeterT, Jan 22 '16 at 14:42
@ScottHoltzman put this in the immediate window: `? format (timeserial(11,24,45.5642),"hh:mm:ss.000")` it still rounds the number and puts `.000` on the end. — Scott Craner, Jan 22 '16 at 14:45

Axel Richter · Accepted Answer · 2016-01-22T15:47:37.627

3

The Format function seems not able to deal with milliseconds.

I would use the Application.Text function instead. But this function needs the first argument being numeric. So we must test this :

Private Sub ComboBox21_Change()

 With ComboBox21
  If IsNumeric(.Value) Then
   .Value = Application.Text(CDbl(.Value), "hh:mm:ss.000")
  End If
 End With

End Sub

Edit:

Since we are changing the .Value of the ComboBox within the _Change event of this ComboBox this will always run twice. Better would be:

Dim stopCBEvents As Boolean

Private Sub ComboBox21_Change()

 If Not stopCBEvents Then
  With ComboBox21
   'MsgBox .Value
   If IsNumeric(.Value) Then
    stopCBEvents = True
    .Value = Application.Text(--.Value, "hh:mm:ss.000")
    stopCBEvents = False
   End If
  End With
 End If

End Sub

edited Jan 22 '16 at 15:47

answered Jan 22 '16 at 14:56

Axel Richter

56,077
6
60
87

Works like a charm! Thank you! Do you have any idea why the linked cell might be showing the selected value rather than the index of that value? Everything that I have found seems to imply that that should NOT be the case. – CPAR05 Jan 22 '16 at 15:59
With an ActiveX ComboBox control, the `LinkedCell` is "The range that is linked to the control's value." per definition. https://support.office.com/en-us/article/Add-a-list-box-or-combo-box-to-a-worksheet-555bee5f-96e6-4047-a469-78e4f1d988b3?CorrelationId=319cd2a1-3a59-4aef-b949-f13325f32716&ui=en-US&rs=en-US&ad=US#bmadd_or_edit_a_combo_box__control_tool – Axel Richter Jan 22 '16 at 16:20
You could have `Me.Range("B2").Value = .ListIndex` within the `Private Sub ComboBox21_Change() ... With ComboBox21...` to put the `.ListIndex` somewhere in the sheet (`Me`) containing the `ComboBox`. – Axel Richter Jan 22 '16 at 16:29

Scott Craner · Answer 2 · 2016-01-22T15:31:50.467

1

You can do something like this:

.Value = Format(.Value, "hh:mm:ss") & Right(Format(.Value * 24 * 60 * 60, "0.0"), 2)

Change the number of zeros and the 2 to the desired length. This will be a string that you will need to convert back to a number. You can do that in excel by referring to the linked cell:

=--A1

edited Jan 22 '16 at 15:31

answered Jan 22 '16 at 14:41

Scott Craner

148,073
10
49
81

Thanks @Scott Craner, this worked to display the tenth of a second value, however it seems to infinitely repeat it (i.e. 20:10:31.8.8.8.8.8.8..etc) Did I miss something here? – CPAR05 Jan 22 '16 at 14:47
1

Did you modify the formula for only 1 digit after decimal? I will edit the answer. – Scott Craner Jan 22 '16 at 14:50
2

@Scott Craner: This takes the first decimal of the `.Value` as 1/10 second. This is not true since the `.Value` is a fraction of a day. It should be: `.Value = Format(.Value, "hh:mm:ss") & Right(Format(.Value * 24 * 60 * 60, "0.0"), 2)` – Axel Richter Jan 22 '16 at 15:14
@Axel you are correc . I am in a meeting and will fix it when I get back. I missed that. – Scott Craner Jan 22 '16 at 15:17

Formatting to display time to millisecond value in combobox and linked cell output

2 Answers2