Format a combo box to date 1

[scottie1]

I've made a combo box with the dates in it but when I select a date it changes it to a different format ie 18-dec-03 = 37973. All help greatly appreciated.

 

[Bowers74]

37973 is the Excel numeric code for Dec. 18th 2003. I am assuming that the value of the date is being returned to a cell.

Where is the date being "sent" to? (i.e. Where does 37973 appear?)



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

in the combo change event use
comboboxname.value = format(comboboxname.value,"dd/mm/yyyy&quot

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[scottie1]

I'm not trying to get a cell to say whats in the combo box The number appears "IN" the combo box but I want it as a date humans recognise.

 

[xlbo]

use my reply then

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Andy,

I most humbly apologize for not understanding the detailed description of your problem.

Geoff's given you the answer you need!

Enjoy! ;-)





Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[scottie1]

I tried your answer Geof but it didn't work. I also want the combo box to revert to the last date I select.

 

[Bowers74]

Andy,

So that we can be able to help you a bit more than we have been able to, try being a little more specific as to what the problem is or post a SMALL part of your code that you have been using thus far. Post the part of your code where you believe the problem is occuring.



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Bowers74]

Ooops Sorry scottie1 I just noticed that I have been calling you Andy. That's the name of the other guy I have been trying to help today.



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[scottie1]

Private Sub ComboBox1_Click()
Temp = ComboBox1.Text
Temp1 = Format(Temp, "dd, mmm, yy&quot
ComboBox1.Text = Temp1
End Sub

Basically I have column A1:A100 as my range for my combo box and when you click on the arrow on the commbo box and select the date you want it changes the format of the date to say 345612 which I know is the computers recognition of this. The above formula does work (which I got of a colleague) but I thought there must be a quicker way of doing this.

 

[xlbo]

Use combo CHANGE not combo CLICK

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

IN DETAIL:

Private Sub ComboBox1_

Change

()
Temp = ComboBox1.Text
Temp1 = Format(Temp, "dd, mmm, yy")
ComboBox1.Text = Temp1
End Sub

Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

we'll get there eventually Mike ;-)
Xmas beer for helping out

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

I hope so!



Ho Ho Ho!!!



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[scottie1]

Mike

I did change it to CHANGE but i thought I could just put in Geoffs formula
comboboxname.value = format(comboboxname.value,"dd/mm/yyyy&quot Which is a lot shorter than mine but it didnt work. The formula that I sent in works with CLICK as well as CHANGE. If you can make the formula shorter then great but don't worry if you cant (i always live in hope)

Thanks all you guys and gals for your help.

 

[Bowers74]

Private Sub ComboBox1_Change()
ComboBox1 = Format(ComboBox1, "dd, mmmm, yy")
End Sub

Short enough?

;-)



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

My code works fine - you must change comboboxname to reflect whatever name your combobox has so if it's called combobox1 for example then:
Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "dd mmm yy&quot
End Sub

will work

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Geoff,

Mine's shorter



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

Yeh...well....... I like to reference things properly ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[scottie1]

Well done Chaps

Sorry no pictures dont know how
Adam