Combo boxes and formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TC - 12 Apr 2006 19:26 GMT

I have inserted several combo boxes into my form. I was able to populate the
dropdown lists by entering the name of the cells (product) in the 'List Fill
Range' on the properties menu of the combo box. I was wondering if anyone
knew of a way to use a formulas in that field? I am trying to use this
formula (=IF(A11="",Products,NA)). Is this possible?

thanks,

tc

Reply to this Message

William Horton - 12 Apr 2006 20:28 GMT

I don't believe you can use a formula in the ListFillRange property.
However, there are 2 options you have.
Option 1 is to change the range of the ListFillRange property using a macro.
You can have all sorts of If statements in the macro.
Option 2 is to have the ListFillRange property reference one range only (Ex
A1:A10). Then in range A1:A10 use regular Excel If formulas to change the
data/values based on whatever your criteria is.

Hope this helps.

Thanks,
Bill Horton

> I have inserted several combo boxes into my form. I was able to populate the
> dropdown lists by entering the name of the cells (product) in the 'List Fill
[quoted text clipped - 5 lines]
>
> tc

Reply to this Message

TC - 12 Apr 2006 20:33 GMT

Bill,

I like the macro solution. Do you have an example I might be able to follow
to create the scenario? Or if you didn't mind please elaborate a bit more on
the solution.

thanks,

tc

> I don't believe you can use a formula in the ListFillRange property.
> However, there are 2 options you have.
[quoted text clipped - 18 lines]
> >
> > tc

Reply to this Message

William Horton - 12 Apr 2006 20:50 GMT

TC,

Sorry I don't have an example handy but you would need to put a macro in
some sort of an event. Perhaps the Enter event of the combobox control you
are using. Then you could put your code to determine what to put in the
ListFillRange property.

If ThisWorkbook.ActiveSheet.Range("$A$11") = "" Then
YourControlName.ControlFormat.ListFillRange = Products
Else
YourControlName.ControlFormat.ListFillRange = NA
End If

The above isn't the exact code that would work but it is the jist of it.
Look at visual basic help or try posting on the Excel Programming forum.

Thanks,
Bill Horton

> Bill,
>
[quoted text clipped - 28 lines]
> > >
> > > tc

Reply to this Message

TC - 12 Apr 2006 23:22 GMT

Bill,

Thank you for the assistance. I will give it a shot.

TC

> TC,
>
[quoted text clipped - 47 lines]
> > > >
> > > > tc

Reply to this Message