VBA Coding of Date and Combo in Excel 2011

**Alexph** · 04-03-2012, 06:18 AM

Hi

i have created a user form which with help from others in the forum is now doing what I want - many thanks. i would like to tweak the form a bit and have two questions

1. I have a date form of the field and cant find out where and how to code the format so that the user is forced to user dd/mm/yyyy
2. On enter of a combo box, is it possible to force a down arrow click so that the combo box automatically extends to display the options.

Thanks
A

**PingPing** · 04-03-2012, 07:27 AM

Originally Posted by Alexph

1. I have a date form of the field and cant find out where and how to code the format so that the user is forced to user dd/mm/yyyy

You could use the DateTimePicker control, instead. In the VBA IDE open the Toolbox (View > Toolbox) and then right-click in the blank space of the Controls tab and you'll see the option "Additional Controls...". Scroll down the list to 'Microsoft Date and Time Picker Control...' and select it. It will now appear on your Toolbox. Next, place a DTP onto your UserForm and have a look at its Properties window. There's a property called 'Format' which you need to set to '3 - dtpCustom'. Then, find the property called 'CustomFormat' and give it the format you want, eg. yyyy-MMM-dd. You should find your DTP control is now showing the date format you want. Additionally, your user now only has to choose the date with a few mouse clicks rather than typing it it.

Originally Posted by Alexph

2. On enter of a combo box, is it possible to force a down arrow click so that the combo box automatically extends to display the options.

The 'ShowDropDownButtonWhen' property of the control has the option '1 - fmShowDropButtonWhenFocus'. Is this what you're looking for?

**Alexph** · 04-03-2012, 08:16 AM

Hi and very many thanks for your help

In relation to the Date Picker, I dont seem to have your options. When I click View Toolbox, I get a grid of 4 x 4 options all filled. I am running a Mac, are you? I think that the interface between Macs and PCs are different.

On the combo box, what i want to happen is that when the combo box gets focus, VBA runs a line of code which is the equivalent to the user pressing the down arrow button and the combo box opens to display the options for selection by the user. Going mack many years to my Access days there was a line of code that used to effect this but its been so long since I did any VBA programming and cannot recall what it was.
Edit: However I have just managed to trace it - Me!cboCommCode.Dropdown

**JosephP** · 04-03-2012, 08:52 AM

I think you're better off using three combos (or two and a text box for year) and then create the date from the values for day, month and year. There aren't any additional controls you can use in 2011, I don't think.

**Alexph** · 04-03-2012, 09:14 AM

Hi, Thanks for your help

i was really just trying to make it easier for the user, and I couldn't find an object so I think I'll just let them enter the date manually

**Jim Gordon Mac MVP** · 04-09-2012, 11:09 AM

The "Additional Controls" are open source Active-X components. Since no one has ported Active-X to the Mac, they simply don't exist and can't be used.

Instead of a combo box, consider using a List Box or on a Dialog Sheet use a Combo List box.