formatting combo boxes

[DrJimmy]

Hi,

I have a combo box where it is linked to a list of dates so that these can be chosen. However it displays the date in the Julian format rather than dd/mm/yyyy that I want.

The source list is in this format and I can't work out how to change the format in the combo box - can someone point me in the right direction please.......?

Cheers

 

[RuralGuy]

I'm not sure what you mean by Julian format but how about copying the SQL for the RowSource of the combobox to a post here so we can see it.

EDIT: Forget my answer. I just noticed this is for Excel.

 

[HiTechCoach]

What is the exact foramt of your Julian date? Is is YYddd?

 

[HiTechCoach]

 *********************************************************************
' from http://support.microsoft.com/kb/209922/en-us
'
' FUNCTION: CJulian2Date()
'
' PURPOSE: Convert a Julian day to a date. The function works with
'          dates based on the Gregorian (modern) calendar.
'
' ARGUMENTS:
'    JulDay: The ordinal day of a year. Between 1 and 365 for all
'            years, or between 1 and 366 for leap years.
'
'    YYYY: A three or four digit integer for a year that is within the
'          range of valid Microsoft Access dates. If YYYY is omitted,
'          then YYYY is assumed to be the year of the current system
'          date.
'
' RETURNS: A date for a valid Microsoft Access year and Julian day,
'          or a Null value for an invalid Julian Day.
' *********************************************************************

Function CJulian2Date (JulDay As Integer, Optional YYYY)
    If IsMissing(YYYY) Then YYYY = Year(Date)
    If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY _
      > 9999 Then Exit Function
    If JulDay > 0 And JulDay < 366 Or JulDay = 366 And _
      YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then _
        CJulian2Date = Format(DateSerial(YYYY, 1, JulDay), "m/d/yyyy")
End Function

You can use this function to create a new column that is format as you want.

To convert a Julian date format like 99032 to a Microsoft Access date:

?CJulian2Date(99032 Mod 1000, 1900 + 99032 \ 1000)

 

[Tanya]

I would personally create a new column next to original list with a formula which converts the julian date to the format you want. Below is a website that shows you have to achieve this.

http://www.cpearson.com/excel/jdates.htm

With the new column, define your list for the combo box.

cheers
Tanya

 

[DrJimmy]

Hi - the list of dates is already in the standard date format. When you click on the drop down button the list is also displayed in the correct format - it's only the date that is in the actual box that is not as I want it. I've attached a screen shot.

Cheers

 

[chergh]

Thats not a Julian date. Microsoft stores dates as integers. One way around this would be to select design mode from the control tool box, double click on the combo, this should take you to the change event for the combo box, and then put the following code in:

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "DD/MM/YYYY")
End Sub

 

[Tanya]

I have put together a test for your problem and it appears as simple as formatting the cell with the dropdown list to date format.

I have attached a copy for you.

cheers
Tanya

 

[chergh]

I think he is using a combox from the control toolbox rather than a validation list.

 

[Tanya]

Ahhh I didn't think of that, silly me. However, while we are on the subject, when would you use a combo in preference to a validation list?

 

[chergh]

If your just wanting to choose a value and use that value then a validation list is probably fine if your wanting to do anything more, e.g. select the name of a word doc and then open it, then you want to use a combobox.

 

[Tanya]

Thank you for that definition.

 

[DrJimmy]

Hi - that piece of code has done the trick. I was using a combo box purely because it's more obvious than a validation list that there is a drop down box - and the end user of the report isn't someone that would either know that there is a drop down list or remember after I've told them that there is one!

Cheers for your help.

 

[DrJimmy]

Hi - now that we're in the new year the code I've used to change the format is now defaulting the year to 2009 even though the range of dates to choose from goes back to 2008. I've tried different settings in the code but nothing seems to work - any ideas?

Cheers

 

[scott-atkinson]

Ahhh I didn't think of that, silly me. However, while we are on the subject, when would you use a combo in preference to a validation list?

I personally find Combo Boxes more user friendly than Validation lists especially when large selection volumes are involved, a Validated list asks the user to search down the list for the selection they need, when you have in excess of a few hundred records this becomes very tedious and time consuming, whereas a Combo Box can be tailored so the user can type the beginning of their chosen selection and the Combo Box will choose the most relevant detail to select, if this is not the correct selection the user can then scroll down or up the list from this point.

Much more friendly.

Scotty

 

[scott-atkinson]

Hi - now that we're in the new year the code I've used to change the format is now defaulting the year to 2009 even though the range of dates to choose from goes back to 2008. I've tried different settings in the code but nothing seems to work - any ideas?

Cheers

Formatting dates in Excel can be a minefield as previous posters have pointed out Excel recognises dates best in Integer format rather than Gregarion format.

I have found when producing VBA applications that dates work best when they have been absolutely validated beyond any doubt.

Try this on the original list and see if it has any impact on the formatted result of the Combo Box.

This first part of the code will select the column that holds the date records, (Change the Column designations to suit your workbook), and will format it into a Gregarion date format, don't worry if it is already in this format.

Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

The next part of this code will make absolutley sure that the data has been formatted into the correct date format. (Again change the Column desigantion to suit).

Set wb_child = ActiveWorkbook
    With wb_child
        With .Sheets(1)
        .Range("B:B").NumberFormat = "dd/mm/yyyy;@"
        End With
        End With

You may need prior to this code to asign the wb_child variable do this with the following code at the start of the code string

Dim wb_child As Workbook

See if all this helps with your formatting problem.

 

[Tanya]

Thank you Scott for this answer.

 

[DrJimmy]

Hi -this has changed things however not to what I'm after. This has changed the format of the original list and has updated the list in the combo box - however it still reverts to 2009. If I remove the original piece of coding that changed the format to mmm-yy for the combo box then it displays it as an integer.

I've attached an example and done the combo boxes with and without - starting to drive me a bit insane this one!

Cheers for your continued help!

 

[Tanya]

Hi, I've been looking at your problem and scouring the internet for answers and can understand your frustration. Sometimes the easiest solution is the best and therefore I suggest you use the validation list option. At least it will save your sanity

 

[DrJimmy]

I've kinda sorted it.

In the properties of the combo box I've changed Style from "0 - fmStyleDropDownCombo" to "2 - fmStyleDropDownList".

This then changes the date format in the linked cell to an integer. To get round this I've used the following code:

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "MMM-YY")

Range("C9").Select

Selection.TextToColumns Destination:=Range("C9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.NumberFormat = "m/d/yyyy"

End Sub

To change it to the format that I want. Perhaps not the tidiest solution but it seems to work and solves my problem so I'll run with it!

Thanks for everyones help. I'll try and think of another ridiculous problem to keep everyone busy!

Cheers once again.