software

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=quot;quot;,Products,NA)). Is this possible?

thanks,

tc

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

quot;TCquot; wrote:

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

quot;William Hortonquot; wrote:

gt; I don't believe you can use a formula in the ListFillRange property.
gt; However, there are 2 options you have.
gt; Option 1 is to change the range of the ListFillRange property using a macro.
gt; You can have all sorts of If statements in the macro.
gt; Option 2 is to have the ListFillRange property reference one range only (Ex
gt; A1:A10). Then in range A1:A10 use regular Excel If formulas to change the
gt; data/values based on whatever your criteria is.
gt;
gt; Hope this helps.
gt;
gt; Thanks,
gt; Bill Horton
gt;
gt; quot;TCquot; wrote:
gt;
gt; gt; I have inserted several combo boxes into my form. I was able to populate the
gt; gt; dropdown lists by entering the name of the cells (product) in the 'List Fill
gt; gt; Range' on the properties menu of the combo box. I was wondering if anyone
gt; gt; knew of a way to use a formulas in that field? I am trying to use this
gt; gt; formula (=IF(A11=quot;quot;,Products,NA)). Is this possible?
gt; gt;
gt; gt; thanks,
gt; gt;
gt; gt; tc

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(quot;$A$11quot;) = quot;quot; 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

quot;TCquot; wrote:

gt; Bill,
gt;
gt; I like the macro solution. Do you have an example I might be able to follow
gt; to create the scenario? Or if you didn't mind please elaborate a bit more on
gt; the solution.
gt;
gt; thanks,
gt;
gt; tc
gt;
gt; quot;William Hortonquot; wrote:
gt;
gt; gt; I don't believe you can use a formula in the ListFillRange property.
gt; gt; However, there are 2 options you have.
gt; gt; Option 1 is to change the range of the ListFillRange property using a macro.
gt; gt; You can have all sorts of If statements in the macro.
gt; gt; Option 2 is to have the ListFillRange property reference one range only (Ex
gt; gt; A1:A10). Then in range A1:A10 use regular Excel If formulas to change the
gt; gt; data/values based on whatever your criteria is.
gt; gt;
gt; gt; Hope this helps.
gt; gt;
gt; gt; Thanks,
gt; gt; Bill Horton
gt; gt;
gt; gt; quot;TCquot; wrote:
gt; gt;
gt; gt; gt; I have inserted several combo boxes into my form. I was able to populate the
gt; gt; gt; dropdown lists by entering the name of the cells (product) in the 'List Fill
gt; gt; gt; Range' on the properties menu of the combo box. I was wondering if anyone
gt; gt; gt; knew of a way to use a formulas in that field? I am trying to use this
gt; gt; gt; formula (=IF(A11=quot;quot;,Products,NA)). Is this possible?
gt; gt; gt;
gt; gt; gt; thanks,
gt; gt; gt;
gt; gt; gt; tc

Bill,

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

TC

quot;William Hortonquot; wrote:

gt; TC,
gt;
gt; Sorry I don't have an example handy but you would need to put a macro in
gt; some sort of an event. Perhaps the Enter event of the combobox control you
gt; are using. Then you could put your code to determine what to put in the
gt; ListFillRange property.
gt;
gt; If ThisWorkbook.ActiveSheet.Range(quot;$A$11quot;) = quot;quot; Then
gt; YourControlName.ControlFormat.ListFillRange = Products
gt; Else
gt; YourControlName.ControlFormat.ListFillRange = NA
gt; End If
gt;
gt; The above isn't the exact code that would work but it is the jist of it.
gt; Look at visual basic help or try posting on the Excel Programming forum.
gt;
gt; Thanks,
gt; Bill Horton
gt;
gt; quot;TCquot; wrote:
gt;
gt; gt; Bill,
gt; gt;
gt; gt; I like the macro solution. Do you have an example I might be able to follow
gt; gt; to create the scenario? Or if you didn't mind please elaborate a bit more on
gt; gt; the solution.
gt; gt;
gt; gt; thanks,
gt; gt;
gt; gt; tc
gt; gt;
gt; gt; quot;William Hortonquot; wrote:
gt; gt;
gt; gt; gt; I don't believe you can use a formula in the ListFillRange property.
gt; gt; gt; However, there are 2 options you have.
gt; gt; gt; Option 1 is to change the range of the ListFillRange property using a macro.
gt; gt; gt; You can have all sorts of If statements in the macro.
gt; gt; gt; Option 2 is to have the ListFillRange property reference one range only (Ex
gt; gt; gt; A1:A10). Then in range A1:A10 use regular Excel If formulas to change the
gt; gt; gt; data/values based on whatever your criteria is.
gt; gt; gt;
gt; gt; gt; Hope this helps.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; Bill Horton
gt; gt; gt;
gt; gt; gt; quot;TCquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have inserted several combo boxes into my form. I was able to populate the
gt; gt; gt; gt; dropdown lists by entering the name of the cells (product) in the 'List Fill
gt; gt; gt; gt; Range' on the properties menu of the combo box. I was wondering if anyone
gt; gt; gt; gt; knew of a way to use a formulas in that field? I am trying to use this
gt; gt; gt; gt; formula (=IF(A11=quot;quot;,Products,NA)). Is this possible?
gt; gt; gt; gt;
gt; gt; gt; gt; thanks,
gt; gt; gt; gt;
gt; gt; gt; gt; tc