Problems with combobox

[VegasNath]

Hi,
I am having difficulties with a combobox on a worksheet.

It's "ListFillRange" is a range of dates dd/mm/yyyy formatted to mmm-yy. In the drop-down menu I see dates as mmm-yy (great), but the selected date then shows as #####, excel's numeric date. Also, the linked cell is populated with mm/dd/yyyy.

Please, what am I doing wrong?

[HansV]

You're running into VBA being US-centric.
Do not set a linked cell, but handle both the display value and the cell in code:

Code: Select all

Private Sub ComboBox1_Click()
    Me.Range("C2") = CDate(Me.ComboBox1.Value)
    Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "dd/mm/yyyy")
End Sub

Replace ComboBox1 with the name of the combo box and C2 with the address of the cell that should display the value of the combo box.

[VegasNath]

Thanks Hans, that works except once the period is selected, it shows in the combobox as dd/mm/yyyy, not mmm-yy. How can I change that?

[HansV]

Change the format in the code that I posted.

[VegasNath]

Change the format in the code that I posted.

I did that but then it messes up the output. If I change to "mmm-yy" then select Jan-07, I get an output of 07/01/2011.

[HansV]

Works OK for me...

x562.png

[VegasNath]

Not for me.... Does the attached workbook 'behave' for you? I have selected Jan-07, but see Jan-11.

[HansV]

Somehow, the code gets called twice in your workbook. Try this version:

Code: Select all

Private fStop As Boolean

Private Sub ComboBox1_Click()
    If fStop = True Then
        fStop = False
        Exit Sub
    End If
    fStop = True
    Me.Range("C2") = CDate(Me.ComboBox1.Value)
    Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "mmm-yy")
End Sub

[VegasNath]

Using a break point, I see what you mean. How bizarre! What the ... would cause that?

[HansV]

Recursive event - changing the value of the combo box calls the On Click event again.

[VegasNath]

Recursive event - changing the value of the combo box calls the On Click event again.

Sorry to keep on with this... Ok, I understand, that makes sense. But why is that not the case for you (re post 46129) ??

[HansV]

I really don't know! Sorry...

[VegasNath]

Ok, Thanks.

[VegasNath]

Back again....
I have switched to a userform set-up, and have a similar problem.

Code: Select all

Private Sub UserForm_Initialize()
    
    For Each Rng In Sheets("Admin").Range("ChooseDate")
        With Me.ComboBox4
            .AddItem Format(Rng.Value, "mmm-yy")
            .ListIndex = 0
        End With
    Next Rng
    
End Sub

This shows the dates in the combo box correctly (mmm-yy). The problem that I am having is later retrieving the selected value back to the worksheet as dd/mm/yyyy.

Code: Select all

Private Sub ComboBox4_Change()

    Sheets("Admin").Range("B5").Value = ComboBox4.Value
    Sheets("Admin").Range("B5").Value = CDate(ComboBox4.Value)
    Sheets("Admin").Range("B5").Value = Format(ComboBox4.Value, "dd/mm/yyyy")
    Sheets("Admin").Range("B5").Value = Format(CDate(ComboBox4.Value), "dd/mm/yyyy")

End Sub

None of the above provide the correct result. Please does anybody know the correct way in which to return the correct result?

[HansV]

You don't declare the variable rng in UserForm_Initialize, which makes me fear that you're still not using Option Explicit...

There is no point in setting ListIndex to 0 in every step of the loop, you only need to do that once, AFTER the loop:

Code: Select all

Private Sub UserForm_Initialize()
    Dim rng As Range
    For Each rng In Sheets("Admin").Range("ChooseDate")
        Me.ComboBox4.AddItem Format(rng.Value, "mmm-yy")
    Next rng
    Me.ComboBox4.ListIndex = 0
End Sub

To set the value of cell B5, you must convert the value of the combo box, which is not a valid date, to a date:

Code: Select all

Private Sub ComboBox4_Change()
    Worksheets("Admin").Range("B5") = DateValue("1-" & Me.ComboBox4)
End Sub

[VegasNath]

Thanks Hans, works perfect. I do use OE but always try and strip my code down to bare relevance before posting, occasionally omitting relevant detail.