Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box returning Array Value instead of Actual List Value
I am using XP 2002, SP1. I typically use Excel to perform simple math but
now am dabbling in some more advanced areas. Please excuse me if I misuse any terms... I am trying to build a Dialog Box which eases data entry for the user while still applying the Validation of List Boxes for data selection. The Dialog Box displays several List Boxes for the user to populate, and the Lists of data are displaying appropriately to the user. However, when I bind the selected values of each box to another cell in my workbook (I'm using the workbook as a source for a mail merge to a word doc), the destination cell is returning the array value instead of the actual value as displayed on the list. For example, the list box shows: dog, cat, mouse. The user selects cat. The value returned in the destination cell is '2' not 'cat'. Can anyone help me out here? TIA Angela |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box returning Array Value instead of Actual List Value
What is the statement that transfers your data from listbox to cell?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box returning Array Value instead of Actual List Value
I'm not sure I can elaborate very well.
I am using the Forms Toolbar to add the Combo Boxes to the Dialog Box. When I want to add the list of values to the Combo Box, I select Format Control. I get a box, (actually a dialog box I think) which has several tabs. I pick the Control tab, which then allows me to specify the Input Range, Cell Link, and Drop Down Lines. It's the Cell Link field that allows me to direct data from Combo Box to cell... I'm digging around trying to understand Combo Boxes and Matching, and Indexing... It's the fact that it's a Combo Box that is throwing back the array position (I think) Since I don't know the code, if I should be in another forum, feel free to send me packing... I can post this in General if that would be more appropriate. Thanks for any help you can give! -Angela "Mark" wrote: What is the statement that transfers your data from listbox to cell? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box returning Array Value instead of Actual List Value
That is (for better or worse) the way the combobox in the forms controls
works on a spreadsheet. What goes into the "Cell Link" is the numerical index of the selected choice, not the choice itself. You can get around this by using the controls from the Control Toolbox, which are ActiveX controls and behave differently - they are more flexible from a programming standpoint, but it also means they are more complex to use. But, here is the usual solution if you need to use the value that was selected. You can write a formula in a cell (different from the "Cell Link") that finds the value from your Input Range. For the example I will assume the input range is vertical (i.e. a column) starting in cell A1 and the cell link is B1 - the formula would be: =OFFSET(A1,B1-1,0) -- - K Dales "ChickenMunk" wrote: I'm not sure I can elaborate very well. I am using the Forms Toolbar to add the Combo Boxes to the Dialog Box. When I want to add the list of values to the Combo Box, I select Format Control. I get a box, (actually a dialog box I think) which has several tabs. I pick the Control tab, which then allows me to specify the Input Range, Cell Link, and Drop Down Lines. It's the Cell Link field that allows me to direct data from Combo Box to cell... I'm digging around trying to understand Combo Boxes and Matching, and Indexing... It's the fact that it's a Combo Box that is throwing back the array position (I think) Since I don't know the code, if I should be in another forum, feel free to send me packing... I can post this in General if that would be more appropriate. Thanks for any help you can give! -Angela "Mark" wrote: What is the statement that transfers your data from listbox to cell? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula/calculation returning a 0 instead of actual sum | Excel Worksheet Functions | |||
Retrieving last actual value in a list | Excel Discussion (Misc queries) | |||
Vlookup: returning the value below the actual row | Excel Discussion (Misc queries) | |||
Returning actual page field changed in pivottable | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |