Combo Box Formatting

[Melissa]

I set up a combo box using the control toolbox. The list is a list of dates,
however once a date is selected it comes back as the number format instead of
the date format. I have formatted the linked cells and the linked list to
show as date but that still hasnt worked.

How do I get this field formatted to show the date not the number?

 

[Jim Thomlinson]

Combo boxes and dates do not get along. The reason is that like text boxes
and such they deal with text and not numbers or dates. The best option is to
use helper columns or such to coerce the dates to text prior to loading them
to the combo box. You then need to use a helper cell to coerce the text back
to a date from the linked cell. Kind of a pain... have you considered using a
date picker control. It is better designed to handle dates...

On the control toolbox hit the icon that looks like a hammer and wrench with
3 dots. Select the Microsoft Date and Time Picker control. It looks like a
standard combo box but it pops up a calendar when you activate the drop
down...

 

[Melissa]

That worked...yay.....Now how can I write the code that once a date is chosen
an assigned macro will run?

The by key codes that I normally use for the combo box are not working the
same.

 

[Dave Peterson]

You could also add the items to the combobox using .additem--and format the
dates anyway you want.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With Worksheets("Sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
.ComboBox1.Clear
.ComboBox1.ListFillRange = "" 'just in case!
For Each myCell In myRng.Cells
'.ComboBox1.AddItem myCell.Text
.ComboBox1.AddItem Format(myCell.Value, "mmmm dd, yyyy")
Next myCell
End With
End Sub

You could use the .text property if the value in the cell was nicely
displayed--or format the date in an unambigous manner.

I'm not sure when you'd want to populate that combobox--when the workbook opens
(use auto_open or workbook_open) or when the worksheet is selected
(worksheet_activate event)????

 

[Jim Thomlinson]

Put the worksheet in design mode by selecting the Ruler and Triangle icon on
the control toolbox. Double click the combo box or date picker and you will
be taken to the VBE where a code stub will be written for you ... Add your
code...