Combo boxes and formulas

[TC]

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

[William Horton]

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

"TC" wrote:

> 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

[TC]

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

"William Horton" wrote:

> 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
>
> "TC" wrote:
>
> > 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

[William Horton]

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

"TC" wrote:

> 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
>
> "William Horton" wrote:
>
> > 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
> >
> > "TC" wrote:
> >
> > > 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

[TC]

Bill,

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

TC

"William Horton" wrote:

> 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
>
> "TC" wrote:
>
> > 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
> >
> > "William Horton" wrote:
> >
> > > 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
> > >
> > > "TC" wrote:
> > >
> > > > 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