Combo Box

elgringo56 · May 4th, 2002, 10:15 AM

I have to be the dumbest guy in the world. Here goes. I have a combo box. The Target cell for the box is Y1. It is formated as Date style 03-Apr-02. The list range for the combox is set to Y48:Y79. Each of these cells contain the formula =TODAY(), =TODAY()-1, etc. The cells are formated as Date style 03-Apr-02 also. Now, when I click the arrow on the drop down box, I see this list. Thats kewl. Now, I select an entry in the list. Here is where I am stuck. The list goes away as it should, but in the combo box appears one of those goofy date numbers that Bill Gates programmers are so fond of. The Target cell also goes to that goofy number. I want the actual date to appear in the target cell and the combox. Any way I can do this? I cant seem to find a format for this?

Tom Schreiner · May 4th, 2002, 10:58 AM

Remove the linked cell reference in your
combobox properties.
Place this code in.
Set the format on the cell to the correct
date format mm-ddd-yy





Private Sub ComboBox1_Change()

    ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy")

    Range("Y1") = Str(Format(ComboBox1.Text, "dd-mmm-yy"))

End Sub

Tom

elgringo56 · May 4th, 2002, 11:00 AM

Thanks heaps, tom. I will give it a try

elgringo56 · May 4th, 2002, 11:09 AM

Tom, When I did as you instructed, and clicked on a date in the drop down, I got a syntax error on the Range("Y1") = str....line. I checked it and it is typed in exact.

elgringo56 · May 4th, 2002, 11:13 AM

OOPS, I found a " that I left out. Now I get a type mismatch on that entry.????

Tom Schreiner · May 4th, 2002, 11:53 AM

I gave you the wrong code...
BRB

Tom Schreiner · May 4th, 2002, 11:54 AM

I gave you the wrong code...
The str() was the problem.
Sorry.





Private Sub ComboBox1_Change()

    ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy")

    Range("Y1") = Format(ComboBox1.Text, "dd-mmm-yy")

End Sub

This is probably obvious, but just in case, make sure you replace ComboBox1 with the name of your combobox...

Tom

[ This Message was edited by: TsTom on 2002-05-04 10:56 ]

elgringo56 · May 4th, 2002, 12:02 PM

Yeppers, Tom, that was it. Works now. Thanks a bunch, thought I was going to have to use a list box for this one and didn't want to.

May 4th, 2002, 10:58 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Remove the linked cell reference in your combobox properties. Place this code in. Set the format on the cell to the correct date format mm-ddd-yy Private Sub ComboBox1_Change() ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy") Range("Y1") = Str(Format(ComboBox1.Text, "dd-mmm-yy")) End Sub Tom

May 4th, 2002, 11:54 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I gave you the wrong code... The str() was the problem. Sorry. Private Sub ComboBox1_Change() ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy") Range("Y1") = Format(ComboBox1.Text, "dd-mmm-yy") End Sub This is probably obvious, but just in case, make sure you replace ComboBox1 with the name of your combobox... Tom [ This Message was edited by: TsTom on 2002-05-04 10:56 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 4th, 2002, 10:15 AM	#1
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	I have to be the dumbest guy in the world. Here goes. I have a combo box. The Target cell for the box is Y1. It is formated as Date style 03-Apr-02. The list range for the combox is set to Y48:Y79. Each of these cells contain the formula =TODAY(), =TODAY()-1, etc. The cells are formated as Date style 03-Apr-02 also. Now, when I click the arrow on the drop down box, I see this list. Thats kewl. Now, I select an entry in the list. Here is where I am stuck. The list goes away as it should, but in the combo box appears one of those goofy date numbers that Bill Gates programmers are so fond of. The Target cell also goes to that goofy number. I want the actual date to appear in the target cell and the combox. Any way I can do this? I cant seem to find a format for this?

May 4th, 2002, 11:00 AM	#3
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	Thanks heaps, tom. I will give it a try

May 4th, 2002, 11:09 AM	#4
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	Tom, When I did as you instructed, and clicked on a date in the drop down, I got a syntax error on the Range("Y1") = str....line. I checked it and it is typed in exact.

May 4th, 2002, 11:13 AM	#5
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	OOPS, I found a " that I left out. Now I get a type mismatch on that entry.????

May 4th, 2002, 11:53 AM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I gave you the wrong code... BRB

May 4th, 2002, 12:02 PM	#8
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	Yeppers, Tom, that was it. Works now. Thanks a bunch, thought I was going to have to use a list box for this one and didn't want to.