Combo Box Time Format

[Aviles]

Hello,

I'm using a combo box in a userform where the drop-down box lists various different times (e.g. 08:55, 09:30, 09:35 etc.) from sheet "Times". These times have already been formatted as "hh:mm" on the Times sheet.

But when the userform comes up, it shows the times in the drop-down list as: 0.371527777777778, 0.395833333333333, 0.399305555555556 etc.

Can some one please show me how to get the times in the user form to come up as format "hh:mm"?

Thanks.

 

[Norie]

How have you populated the combobox?

 

[Aviles]

Below is the code for the userform. The combo box is called "cmbTime".

Option Explicit
 
Private Sub UserForm_Activate()
    'Re-sets all the fields so that the values previously entered are re-set
    txtTradeDate.Text = Format(Now() - 8 / 24, "dd/mm/yyyy")
    txtContactName.Text = ""
    cmbTime = ""
    cmbEnterClientFM = ""    

    cmbEnterClientFM.SetFocus
        
End Sub
 
Private Sub UserForm_Initialize()
 
    Dim cLoc As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Lists")
    Dim wsw As Worksheet
    Set wsw = Worksheets("TimeLists")
    Dim cell As Range
    
    For Each cLoc In ws.Range("ClientList")
    
    Next cLoc
    
    For Each cLoc In ws.Range("ClientList")
      With Me.cmbEnterClientFM
    End With
    
    Next cLoc
    
    For Each cLoc In ws.Range("ClientList")
      With Me.cmbEnterClientFM
        .AddItem cLoc.Value
    End With
    Next cLoc
    
    For Each cLoc In wsw.Range("TimeList")
    
    Next cLoc
    
    For Each cLoc In wsw.Range("TimeList")
      With Me.cmbTime
    End With
    
    Next cLoc
    
    For Each cLoc In wsw.Range("TimeList")
      With Me.cmbTime
        .AddItem cLoc.Value
    End With
    Next cLoc
    
End Sub

I also have the below code in the "Time" sheet:

Option Explicit
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
'This macro creates the dynamic drop-down list of Clients/FM in the user form
Dim r As Long
Dim wsL As Worksheet
Dim lLastRow As Long
  
  'Ignore selection of multiple cells
  If Target.Count > 1 Then Exit Sub
  
  'Ensure that we need to register the change
  If Target.Column = 1 And Target.Row > 2 Then
    Set wsL = Sheets("TimeLists")
    
    'Clear the old list
    wsL.Range("A1").CurrentRegion.ClearContents
    
    'Get the last row of clients
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Create a unique list of clients in the "List" sheet
    Range(Cells(2, 1), Cells(lLastRow, 1)) _
    .AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=wsL.Range("A1"), Unique:=True
    
    'Sort clients alphabetically
    r = wsL.Cells(Rows.Count, 1).End(xlUp).Row
    
    wsL.Range(wsL.Cells(1, 1), wsL.Cells(r, 1)).Sort _
    Key1:=wsL.Range("A1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    
    'Dynamically create a named range to capture the client list
    ActiveWorkbook.Names.Add Name:="TimeDataValList", _
    RefersToR1C1:="=Lists!R1C1:R" & r & "C1"
    
    'Create a drop down, populated with the unie client list
    With Target.Validation
      .Delete
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Operator:=xlBetween, _
      Formula1:="=DataValList"
      .IgnoreBlank = True
      .InCellDropdown = True
      .ShowError = False
    End With
    
    End If
End Sub

 

[Norie]

Try this, it'll display the times as they are formatted on the worksheet.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim wsw As Worksheet
Dim cLoc As Range
 
    Set wsw = Worksheets("TimeLists")

    Set ws = Worksheets("Lists")
 
    For Each cLoc In ws.Range("ClientList")
        With Me.cmbEnterClientFM
            .AddItem cLoc.Value
        End With
    Next cLoc

    For Each cLoc In wsw.Range("TimeList")

        With Me.cmbTime
            .AddItem cLoc.Text
        End With

    Next cLoc
 
End Sub

 

[Aviles]

Thanks Norie! That worked great.