Combo Box

elgringo56

Well-known Member
Joined
Apr 15, 2002
Messages
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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

<pre>

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

</pre>

Tom
 
Upvote 0
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.
 
Upvote 0
I gave you the wrong code...
The str() was the problem.
Sorry.<pre>

Private Sub ComboBox1_Change()
ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy")
Range("Y1") = Format(ComboBox1.Text, "dd-mmm-yy")
End Sub</pre>

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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top