Date format on a drop down box
 

I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I have
tried to reformat the cell to be mm/dd/yyyy manually but without any success.

Should be a simple reason but I am stumped here.

Hi

You have to format the cell' where you select the date, too. Formatting the
data validation list range determines only, how are selections in drop-down
displayed. You can format the destination cell in same way, or differently,
as you like. It's mystery to me, why you didn't success with it - maybe you
try again.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Steven Cheng" wrote in message
...
I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I
have
tried to reformat the cell to be mm/dd/yyyy manually but without any
success.

Should be a simple reason but I am stumped here.

Is this a dropdown from the Forms toolbar or a combobox from the control toolbox
toolbar?

When I tried a dropdown from the Forms toolbar, I saw the dates listed
nicely--both in the dropdown and when it was selected (in the dropdown).

But with the combobox from the control toolbox toolbar, the chosen value looked
like the date's serial value.

But I could format the value the way I wanted if I used code.

Go into design mode (an icon on that same control toolbox toolbar).
double click on your combobox.
paste this code in:

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()
If blkProc = True Then Exit Sub
With Me.ComboBox1
blkProc = True
.Value = Format(.Value, "mm/dd/yyyy")
blkProc = False
End With
End Sub

(I used combobox1. Change that if necessary.)

Steven Cheng wrote:

I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I have
tried to reformat the cell to be mm/dd/yyyy manually but without any success.

Should be a simple reason but I am stumped here.

--

Dave Peterson

Dave;

It is a combobox from the control toolbox. I didn't use the forms one.

As for the combobox, I used the code and it works. It's just that I have a
vlookup function dependent up the value in the linked cell and it is giving
me a N/A error. The date list that is filling the range starts with a
beginning period start date which is entered in mm/dd/yyyy and there is a
formula to increase it one day. If I override the formula and put the dates
in manually, the combobox's linked cell formats the date correctly. But the
vlookup values are still returning an N/A error.

I have formated the date and tested to ensure that it is a number. Both
work out fine, however, the lingering problem is this list. I wonder if it
has something to do with the file itself. This file was originally a Lotus
file. Just speculating but could this be an issue? I have turned off the
translations but could there be something else....inquiring minds want to
know.

Steven

"Dave Peterson" wrote:

Is this a dropdown from the Forms toolbar or a combobox from the control toolbox
toolbar?

When I tried a dropdown from the Forms toolbar, I saw the dates listed
nicely--both in the dropdown and when it was selected (in the dropdown).

But with the combobox from the control toolbox toolbar, the chosen value looked
like the date's serial value.

But I could format the value the way I wanted if I used code.

Go into design mode (an icon on that same control toolbox toolbar).
double click on your combobox.
paste this code in:

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()
If blkProc = True Then Exit Sub
With Me.ComboBox1
blkProc = True
.Value = Format(.Value, "mm/dd/yyyy")
blkProc = False
End With
End Sub

(I used combobox1. Change that if necessary.)

Steven Cheng wrote:

I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I have
tried to reformat the cell to be mm/dd/yyyy manually but without any success.

Should be a simple reason but I am stumped here.

--

Dave Peterson

Dave;

I found this on the knowledgebase. Would this explain why?

http://support.microsoft.com/default...b;en-us;170442

"Dave Peterson" wrote:

Is this a dropdown from the Forms toolbar or a combobox from the control toolbox
toolbar?

When I tried a dropdown from the Forms toolbar, I saw the dates listed
nicely--both in the dropdown and when it was selected (in the dropdown).

But with the combobox from the control toolbox toolbar, the chosen value looked
like the date's serial value.

But I could format the value the way I wanted if I used code.

Go into design mode (an icon on that same control toolbox toolbar).
double click on your combobox.
paste this code in:

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()
If blkProc = True Then Exit Sub
With Me.ComboBox1
blkProc = True
.Value = Format(.Value, "mm/dd/yyyy")
blkProc = False
End With
End Sub

(I used combobox1. Change that if necessary.)

Steven Cheng wrote:

I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I have
tried to reformat the cell to be mm/dd/yyyy manually but without any success.

Should be a simple reason but I am stumped here.

--

Dave Peterson

I don't know why your formula isn't incrementing the dates correctly.

You may want to post the formula.

If you format those date cells as General, do you see any decimals?

Do the same thing with the list of dates that you use for the input range.



Steven Cheng wrote:

Dave;

It is a combobox from the control toolbox. I didn't use the forms one.

As for the combobox, I used the code and it works. It's just that I have a
vlookup function dependent up the value in the linked cell and it is giving
me a N/A error. The date list that is filling the range starts with a
beginning period start date which is entered in mm/dd/yyyy and there is a
formula to increase it one day. If I override the formula and put the dates
in manually, the combobox's linked cell formats the date correctly. But the
vlookup values are still returning an N/A error.

I have formated the date and tested to ensure that it is a number. Both
work out fine, however, the lingering problem is this list. I wonder if it
has something to do with the file itself. This file was originally a Lotus
file. Just speculating but could this be an issue? I have turned off the
translations but could there be something else....inquiring minds want to
know.

Steven

"Dave Peterson" wrote:

Is this a dropdown from the Forms toolbar or a combobox from the control toolbox
toolbar?

When I tried a dropdown from the Forms toolbar, I saw the dates listed
nicely--both in the dropdown and when it was selected (in the dropdown).

But with the combobox from the control toolbox toolbar, the chosen value looked
like the date's serial value.

But I could format the value the way I wanted if I used code.

Go into design mode (an icon on that same control toolbox toolbar).
double click on your combobox.
paste this code in:

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()
If blkProc = True Then Exit Sub
With Me.ComboBox1
blkProc = True
.Value = Format(.Value, "mm/dd/yyyy")
blkProc = False
End With
End Sub

(I used combobox1. Change that if necessary.)

Steven Cheng wrote:

I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I have
tried to reformat the cell to be mm/dd/yyyy manually but without any success.

Should be a simple reason but I am stumped here.

--

Dave Peterson


--

Dave Peterson

It sure looks like it describes your original problem--not the =vlookup() one,
though.

Steven Cheng wrote:

Dave;

I found this on the knowledgebase. Would this explain why?

http://support.microsoft.com/default...b;en-us;170442

"Dave Peterson" wrote:

Is this a dropdown from the Forms toolbar or a combobox from the control toolbox
toolbar?

When I tried a dropdown from the Forms toolbar, I saw the dates listed
nicely--both in the dropdown and when it was selected (in the dropdown).

But with the combobox from the control toolbox toolbar, the chosen value looked
like the date's serial value.

But I could format the value the way I wanted if I used code.

Go into design mode (an icon on that same control toolbox toolbar).
double click on your combobox.
paste this code in:

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()
If blkProc = True Then Exit Sub
With Me.ComboBox1
blkProc = True
.Value = Format(.Value, "mm/dd/yyyy")
blkProc = False
End With
End Sub

(I used combobox1. Change that if necessary.)

Steven Cheng wrote:

I have a dropdown box that is linked to a cell where a date is put that is
used for other things. The drop down box is filling itself from a range,
which contains a sequential list of dates. I have verified that the dates
are indeed dates and not text values. However, whenever I select a date
using the drop down list, the link cell reverts to a serial format. I have
tried to reformat the cell to be mm/dd/yyyy manually but without any success.

Should be a simple reason but I am stumped here.

--

Dave Peterson


--

Dave Peterson