Combobox- Control Toolbox

[abehle]

My issue is:

I have an Excel sheet with a graph that is automatically updated when the user chooses two dates via drop-down comboboxes. There are around 100 dates in each combobox that the user can choose. Originally, I had these dropdowns set up as validation fields and it worked just fine; however, the down arrows only appeared when the validation cell was selected and I need the arrow to show all of the time so the user is aware that it is a drop-down. So, then I tried to do it via the combobox on the Forms toolbar. This worked great except that I could not format the font. This is a very large spreadsheet and is viewed at 75%, so this would not work either as the font on the drop-downs was much smaller than the rest of the font on the worksheet and hard to read. Lastly, I decided to try the combo-box on the Control Toolbox toolbar. This works great- EXCEPT that after selecting a date, the combobox converst the date from mm/dd/yy format to the numerical date. Is there any way to make it stay in mm/dd/yy format?

Additionally, while I'm fairly comfortable in Excel I have not had much experience with programming- so if the solution DOES involve programming please be very clear with instructions on where to go to insert the code! Thanks in advance for any help I can get- I've been struggling with this for some time now!

Anna

[dominicb]

Good morning abehle

...and welcome to the forum!!

The solution does involve programming...
...but it's really easy

Go into design mode from the Control Toolbox, and then double click rapidly on your control. It should open a new window. Delete the bit of code that's in there (there will probably be just two lines) and copy in the code from below :

Please Login or Register  to view this content.

My code assumes your control is called ComboBox1 - you may have to change this.
Now click in the big "X" in the corner to close the VBE and you should be back to Excel. Exit design mode and everything should work OK.

HTH

DominicB

[abehle]

Thanks, but I have actually come across that solution before.... I get the following error when actually trying to select from the drop-down:

Run Time Error '380':
Could not set the Value property. Invalid property value.

Any idea on how to address this issue? Thanks.

[abehle]

Nevermind... I figured it out. I did not have some of my dates formatted in the "mm/dd/yy" format (for example, I had 3/31/08 rather than 03/31/08) and that was causing the error. I've corrected now. Thanks for your help!

[AlanAnderson]

Hi,
I have a Combobox in a form (cmbDate) that looks up a list of dates from a sheet formatted (dd/mm.yy)
When I select a date it displays correctly but when I select a date from the list it then converts it to a number and saves it in that format.

Ive tried to format the combobox using the following code:

Please Login or Register  to view this content.

Neither works. In first case I get error "Wrong number of arguments or invalid property assignment" with the word "Format" highlighted. In second case I get error "Method or data member not found": with the second ".Date" highlighted.

All I need to do is to be able to format the combobox field so that when it reads or writes a value it will be in the correct format.

I suspect I'm going to have the same issue with my amounts (number) fields
Regards,

Alan

[Andy Pope]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[dominicb]

Good morning AlanAnderson

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

DominicB