Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The value of a ListBox Item
Hi
If for example a Listbox was populate with the following list: The Item's name in the first col and the items value in the second A1 B1 Item Sq Ft Per Piece Brick Pavers .33 Plywood 32 Concrete Per Yard 80 Whatever 1 Then elsewhere in the workbook an item is picked from the listbox as illustrated below. The area B1*C1 is divided by its value to get the amount needed. How, can the value of the item picked (in this case plywood -32-) be written in the formula in d1? A1 B1 C1 D1 Item Length Width Amount Needed Plywood 8 8 =(B1*C1) / "the value of plywood" The answer is 2 of course. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The value of a ListBox Item
Hi TK,
Assuming: - you use a Listbox from the Control Toolbox toolbar and not from the Forms toolbar. - the control is on a sheet 1.Display the Properties window. - select the control - click the Properties icon on the COntrol Toolbox toolbars, or right-click and choose Properties 2. Set the listbox - In the Properties window, set the ColumnCount property to 2 to display the 2 columns A and B - set the BoundColumn to 2 so that the Value property of the listbox will return the data from 2nd column (instead of usual default 1st col) - set thr ListFillRange property to sheet1!A2:B5 to dsplay these vales in the List (assuming the list data are in sheet1 range A1:B5) - set the LinkedCell to a cell where the selected value in the List will be sent: sayt to sheet2!A1 if you want the value sent to that address 3. Formula To return to your formula, since sheet2!A1 contains now the value selected in the Listbox you can just use: = (B1*C1) / sheet2!A1 Now if your control is in a Userform, some of these properties' names are different: - replace ListFillRange by RowSource - replace LinkedCell by ControlSource Regards, Sebastien "TK" wrote: Hi If for example a Listbox was populate with the following list: The Item's name in the first col and the items value in the second A1 B1 Item Sq Ft Per Piece Brick Pavers .33 Plywood 32 Concrete Per Yard 80 Whatever 1 Then elsewhere in the workbook an item is picked from the listbox as illustrated below. The area B1*C1 is divided by its value to get the amount needed. How, can the value of the item picked (in this case plywood -32-) be written in the formula in d1? A1 B1 C1 D1 Item Length Width Amount Needed Plywood 8 8 =(B1*C1) / "the value of plywood" The answer is 2 of course. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The value of a ListBox Item
Sebastienm "Thank you" I haven't had time to work the procedure yet but I
have a hard copy and will work on it tomorrow. Thanks again: "sebastienm" wrote: Hi TK, Assuming: - you use a Listbox from the Control Toolbox toolbar and not from the Forms toolbar. - the control is on a sheet 1.Display the Properties window. - select the control - click the Properties icon on the COntrol Toolbox toolbars, or right-click and choose Properties 2. Set the listbox - In the Properties window, set the ColumnCount property to 2 to display the 2 columns A and B - set the BoundColumn to 2 so that the Value property of the listbox will return the data from 2nd column (instead of usual default 1st col) - set thr ListFillRange property to sheet1!A2:B5 to dsplay these vales in the List (assuming the list data are in sheet1 range A1:B5) - set the LinkedCell to a cell where the selected value in the List will be sent: sayt to sheet2!A1 if you want the value sent to that address 3. Formula To return to your formula, since sheet2!A1 contains now the value selected in the Listbox you can just use: = (B1*C1) / sheet2!A1 Now if your control is in a Userform, some of these properties' names are different: - replace ListFillRange by RowSource - replace LinkedCell by ControlSource Regards, Sebastien "TK" wrote: Hi If for example a Listbox was populate with the following list: The Item's name in the first col and the items value in the second A1 B1 Item Sq Ft Per Piece Brick Pavers .33 Plywood 32 Concrete Per Yard 80 Whatever 1 Then elsewhere in the workbook an item is picked from the listbox as illustrated below. The area B1*C1 is divided by its value to get the amount needed. How, can the value of the item picked (in this case plywood -32-) be written in the formula in d1? A1 B1 C1 D1 Item Length Width Amount Needed Plywood 8 8 =(B1*C1) / "the value of plywood" The answer is 2 of course. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last Item of Listbox not getting displayed | Excel Discussion (Misc queries) | |||
Adding item in listbox | Excel Worksheet Functions | |||
Adding item in listbox | Excel Worksheet Functions | |||
Item order in ListBox | Excel Discussion (Misc queries) | |||
Retrieve item names in ListBox | Excel Programming |