Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combo boxes and formulas
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combo boxes and formulas
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combo boxes and formulas
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combo boxes and formulas
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combo boxes and formulas
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cascading combo boxes for project | New Users to Excel | |||
filling combo boxes from single outer source | Excel Discussion (Misc queries) | |||
Formulas in text boxes ? | Excel Discussion (Misc queries) | |||
need a refresher: text boxes on charts that use relational formulas? | Charts and Charting in Excel | |||
Convert Formulas to Values and Preserve Formatting | Excel Discussion (Misc queries) |