VBA Date format

[Trueblue862]

Hi, I have this piece of code listed below, I'm using it to simply search a roster by date and populate a userform with what shifts are being worked by who on a certain date. The only problem I'm having is with my search combo box, "cbxDate", it returns the date format as "mm/dd/yyyy" regardless of how it is input on the spreadsheet. I have tried a number of things but I haven't been able to get it work. I'm all out of ideas, any help would be greatly appreciated.

Option Explicit
Dim mvListTbl As Variant, mvName As Variant
Private Sub UserForm_Activate()
   
    LoadCombos
  

End Sub
Private Sub btnClear_click()
ClearForm
End Sub
Private Sub btnCancel_Click()
' cancel button pressed. Just close
    Unload Me
    
End Sub

Private Sub cbxDate_change()
'Item selection combo box changed. Runs when user selects item
 
    Dim iSelected As String, j As Integer
    
    
    'check if item selected (user could have cleared entry), and load details
    If Len(cbxDate.Value) = 0 Then Exit Sub
    iSelected = cbxDate.ListIndex + 1 'listindex starts at 0 for 1st item, mvItemsTbl starts at 2 (1 is the header)
    'load details into textboxes and unit combo
    tbxSloppy = mvListTbl(iSelected, 3)
    tbxJade = mvListTbl(iSelected, 4)
    tbxAlex = mvListTbl(iSelected, 5)
    tbxZemek = mvListTbl(iSelected, 6)
    tbxKerry = mvListTbl(iSelected, 7)
    tbxGleich = mvListTbl(iSelected, 8)
    tbxCorky = mvListTbl(iSelected, 9)
    tbxAntonio = mvListTbl(iSelected, 10)
    tbxMeyrick = mvListTbl(iSelected, 11)
    tbxSads = mvListTbl(iSelected, 12)
    tbxGotty = mvListTbl(iSelected, 13)
    tbxClarky = mvListTbl(iSelected, 14)
    tbxPutty = mvListTbl(iSelected, 15)
    tbxBeth = mvListTbl(iSelected, 16)
    

   
End Sub




Private Sub ClearForm()
'loop through the controls and set to empty
    Dim ctlBox As MSForms.Control
    
    For Each ctlBox In Me.Controls
        If TypeName(ctlBox) = "TextBox" Or TypeName(ctlBox) = "ComboBox" Then
            ctlBox.Value = ""
        End If
    Next ctlBox

End Sub


Private Sub LoadCombos()
'refresh the arrays. load the item combo. This needs to be done at _
 start and after every edit or sort
    Dim lR As Long
    
    'load columns A:C  and O into an arrays for fast processing
    With Sheets("Roster")
        
        lR = .Range("B1").CurrentRegion.Rows.Count
        mvListTbl = .Range("A1:P" & lR).Value
        
    End With
    
    'relaod combo
    With cbxDate
        .Clear
        
        For lR = 1 To UBound(mvListTbl, 1) 'skip the header row
            .AddItem mvListTbl(lR, 1)
                    Next lR
        
        .SetFocus
    End With

End Sub

 

[mamady]

Hi Dear,

I am not an expert in VBA, but have you tried to add the below to your code

cbxDate = Format(cbxDate, "dd-mmm-yy")

of any other preferred date format

Best Regards
M. Yusuf

 

[Trueblue862]

I've tried that, but I can't get it to work, I'm likely inputting it into the wrong place, but I'm not sure. I'm only just learning this stuff myself.

 

[Trueblue862]

This is the piece of code I have that populates the combo box from the spreadsheet, I have added the line of code to format the combo box in here but it appears to do nothing.

Private Sub LoadCombos()
'refresh the arrays. load the item combo. This needs to be done at _
 start and after every edit or sort
    Dim lR As Long
       
    With Sheets("Roster")
        .Unprotect
        lR = .Range("B1").CurrentRegion.Rows.Count
        mvListTbl = .Range("A1:P" & lR).Value
        .Protect
        
    End With
    With cbxDate
           
             cbxDate = Format(cbxDate, "dd/mm/yyyy")
    End With
    'reload combo
    With cbxDate
        .Clear
             
             For lR = 1 To UBound(mvListTbl, 1) 'skip the header row
            .AddItem mvListTbl(lR, 1)
            
                    Next lR
        .SetFocus
    End With

End Sub

 

[Rick Rothstein]

This is the piece of code I have that populates the combo box from the spreadsheet, I have added the line of code to format the combo box in here but it appears to do nothing.

Private Sub LoadCombos()
'refresh the arrays. load the item combo. This needs to be done at _
start and after every edit or sort
    Dim lR As Long
      
    With Sheets("Roster")
        .Unprotect
        lR = .Range("B1").CurrentRegion.Rows.Count
        mvListTbl = .Range("A1:P" & lR).Value
        .Protect
       
    End With
    With cbxDate
          
             cbxDate = Format(cbxDate, "dd/mm/yyyy")
    End With
    'reload combo
    With cbxDate
        .Clear
            
             For lR = 1 To UBound(mvListTbl, 1) 'skip the header row
            .AddItem Format(mvListTbl(lR, 1), "dd/mm/yyyy")
           
                    Next lR
        .SetFocus
    End With

End Sub

The key is to apply the formatting as you add the item to the ComboBox. With that in mind, give the following a try... remove what I highlighted in red as it is doing nothing and add what I show in green above.

 

[Trueblue862]

Rick Rothstein, thank you very much, I knew it would be something simple like that, I've been stuck on that for a couple of day now.