In VBA, there is a limitation with respect to a Dropdown List. Well, at least for me it looked like a limitation. I am interested in knowing any information on this, though.

I was recently asked about an issue, with respect the Dropdown List. This user has added a Dropdown List to a form using Modifier. This Dropdown List contains certain number of static texts. Now, a field in that form is disabled and the value for this field will come from the Dropdown List. Meaning, the user is restricted in entering any value to this field.

Problem is, to assign the static text from that Dropdown List using a VBA code, seems to be an issue. A typical VBA statement that we would think of writing to assign the selected value of Dropdown List to the field is like follows:

[FieldName].Value = [DropdownList].Value

But it does not work out. [DropdownList].Value returns the INDEX of the selected text and not the TEXT itself. There is no function in VBA, such as itemname() function in Dexterity, to get the text. Is there any workaround? Only workaround that I can think of, is to have a SELECT CASE statement. Something like below:

SELECT CASE [DropdownList].Value
CASE 1 [FieldName].Value = “[Static Text Value in that Position]”
CASE 2 [FieldName].Value = “[Static Text Value in that Position]”
… … …
END SELECT

The only drawback in this method: You have to add a line to this SELECT CASE block, whenever you add a Static Text to that Dropdown List. Quite manageable to some extent.

What about using a Combobox? For a Combobox, instead of a Dropdown List, the statement:

[Fieldname].Value = [Combobox].Value

will work just fine. But the problem is, it is editable. User can enter anything on that Combobox at runtime and the same is going to be assigned to the field. What’s the use of this? It is as good as directly entering the value on to the field.

A sample VBA work is uploaded on this link: VBA-Dropdown-GLEntryForm. Do go thru’ this and get back with your feedback and your valuable inputs.

VAIDY