Format ActiveX Dropdown Display

[Mayanwolfe]

Hello you helpful folks,

I have an ActiveX dropdown box from which the user may select a date formatted thusly: MM/DD/YYYY. The dates display perfectly in the dropdown list, but once one is selected, it shows up as the Excel numeral code in the box (such as 41548). How do I change this so that the selected date shows up in the form MM/DD/YYYY?

Thanks!

 

[Mayanwolfe]

bump

 

[Mayanwolfe]

[SOLVED] This seems to be a fairly common problem, so in case anyone else is wondering, I have found the solution:

1. In the Developer tab, put the sheet into Design Mode.
2. Right-click on your ComboBox and select View Code
3. In the VBA window, enter the following code (replace the name DateBox with whatever you have named your ComboBox):

Private Sub DateBox_Change()
DateBox.Value = Format(DateBox.Value, "mm/dd/yyyy")
End Sub

4. Close the VBA window, take the sheet out of Design Mode, and select dates at your leisure!

You can modify the "mm/dd/yyyy" format in the code to whatever your display needs may be, and output the result to a linked cell for use in subsequent formulas. However, be advised that the output will be read as text and not as a date and therefore could cause problems if you try to read it directly into a formula. To fix this issue, in an adjacent cell, enter this formula (let's say the ComboBox is linked to A1): =--A1. The result will be a date that you may format and use in formulas.

Hope that may help someone!
Mayanwolfe