Changing date format in combo drop down box

[wentworth16238]

Hi,

I've tried a number of ways to do this so far, without much success.

I have a combo box on a userform. I populate it by put the range in the RowSource of the properties for that combo box. In this case I use EFD, effective dates
and they ranges from 02/01/17- 01-01-19

When I choose the date in the combo box I get a 5 digit number.

so, I've tried putting in VBA language suchas
(Code)
Private Sub CbEFD_Change()
CbEFD.Value = Format(CbEFD.Value, "dd-mm-yyyy")
End Sub

This doesn't do anything apparently.

Maybe I'm putting it in the WRONG place, Any ideas?

I've also tried putting some language in the initialize section
I followed something I found on another forum
(Code)

Private Sub Userform_Initalize()

Dim rCell as Range
Dim rInput As Range

Set rInput = Worksheets.Range("EFD") ' I didn't know if I should put in E2:E31 or not
For Each rCell in rInput
CbEFD.Additem. Str(rCell.Value)
Next

Set rCell = Nothing
Set rInput = Nothing

End sub

'This didn't do anything either. Ideas? I'm at a standstill on something this simple.

 

[Momentman]

How are the dates formatted in the excel sheet? Are they formatted as date,I just used "dd-mmm-yy" and on loading the combobox, it comes up in that exact format

 

[wentworth16238]

hi, the dates are formatted as date in each cell of the range EFD

so maybe im putting the language wrong in the userform or I need do something else.

im not sure "where" to put the language

ideas?

 

[wentworth16238]

I loaded the combobox via the RowSource property in the properties box of the userform.

When I do that the dates show up on the drop down, but I gtet a 5 digit number when I choose a date, and thusly when transferred to the excel sheet
the 5 digit number shows up instead of the date, ie 0/01/17

 

[Momentman]

I loaded the combobox via the RowSource property in the properties box of the userform.

When I do that the dates show up on the drop down, but I gtet a 5 digit number when I choose a date, and thusly when transferred to the excel sheet
the 5 digit number shows up instead of the date, ie 0/01/17

Use this then

Private Sub CBEFD_change()
    CBEFD.Text = Format(CBEFD.text, "dd/mm/yy")
End Sub

 

[wentworth16238]

HI, I think I have tried some version of that, but the problem I may be running into is WHERE to put it.

I've tried putting it in the initialization on Userform, or under the button_Click that sends the info to the sheet.

I get runtime errors when I do dthat.

I'm almost ready to start the whole deal over.. lol

Any ideas? thanks for all your help.. I will go back and try to put in the Change sub first.

T

 

[wentworth16238]

Thanks, I tried that one.

I put it in the language under the Submit_Click() button verbiage, but it throws out an error.

so I left that verbiage in but took out the CbEFD in the row source of the properties pane.

I can't seem to get the right combination so that when I use the userform and choose a 02/01/2017 from the drop down I should get the same on the userform and the excel sheet. I don't obviously. I get a 5 digit number.

I'm sure this is pretty common and is easy to fix, but I'm having a hard time..

Any more ideas?