Dialog Box returning Array Value instead of Actual List Value

[ChickenMunk]

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

[Mark]

What is the statement that transfers your data from listbox to cell?

[ChickenMunk]

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?
>
>

[K Dales]

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?
> >
> >