VBA Combo Box

[Guest]

I normally use a format control drop down box. This is my first time using a
VBA Combo Box. I have two questions that are probably very basic but I can't
get this figured out.

I am using ListFillRange to define a column that has dates. This is
working, however the selection is displayed as a seriel number.

How can I format the display of the user's selection to be in a mmm-yy format?

Also, how do I link a cell to pull the selection from the combo-box?

Thanks in advance for any assistance.

 

[Dave Peterson]

You may want to drop the .listfillrange and .linkedcell and just use code to
populate the combobox and the cell.

I put all this code under the worksheet that held the combobox.

Option Explicit
Private Sub ComboBox1_Change()
With Me.Range("b1")
.NumberFormat = "@" 'text
.Value = Me.ComboBox1.Value
End With
End Sub
Private Sub Worksheet_Activate()
Dim myCell As Range
With Me.ComboBox1
.ListFillRange = ""
.Clear
.Style = fmStyleDropDownList
For Each myCell In Worksheets("sheet2").Range("a1:a10").Cells
.AddItem Format(myCell.Value, "mmm-yy")
Next myCell
End With
End Sub

My "listfillrange" was on Sheet2 in A1:A10.
My "linked" cell was on the same sheet (B1).

Note that by showing mmm-yy, you're losing the date (day of month). That's why
I put the value in as text--not a real date.

And I populated the combobox when the sheet is activated. That may not work for
you. You may want to put that code in the Workbook_Open event (under
ThisWorkbook).

This line:
with me.combobox1
will change to:
with worksheets("sheet1").combobox1

 

[Guest]

I have run into this problem before and I will be interested if anyone has a
better answer but here is my workaround:-

Format the dates to text. In VBA it is =Format(yourdate,"dd mmm yyyy")

In the interactive mode on the worksheet it is =TEXT(yourdate,"dd mmm yyyy")

You can use any valid format between the double quotes.

The reverse in VBA to get a date value is Cdate(String)

In the interactivemode VALUE(string)


Regards,

OssieMac